change change a numberic result from select

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

change change a numberic result from select

Author

Topic

awebb
Starting Member

1 Post

Posted - 2010-02-16 : 13:04:51

Hi, How do I change the data in a select column from a number to a specific character. ie I need number 1 to change to a and number 2 to change to b.

thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 13:11:24

you need to use a set of case when statements
or
create a mapping table with values and corresponding alphabets and take join with it on number values and add alphabet column in select list.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-16 : 13:20:54

Depending on your needs you can might be able to do a simple ASCII to CHAR conversion.. Or you might need to map as Visakh has suggested. For example:

DECLARE @Foo TABLE (Val INT)

INSERT @Foo
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4


SELECT Val, CHAR(96 + Val) AS NewChar
FROM @Foo

-- OR

SELECT 
	Val, 
	CASE	
		WHEN Val BETWEEN 1 AND 3 THEN CHAR(96 + Val) 
		ELSE CAST(Val AS CHAR(1))
	END AS NewChar
FROM @Foo

-- OR

SELECT
	Val,
	CASE
		WHEN Val = 1 THEN 'a'
		WHEN Val = 2 THEN 'b'
		WHEN Val = 3 THEN 'c'
		ELSE CAST(Val AS CHAR(1)) 
	END AS NewChar
FROM @Foo

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-17 : 02:06:20

quote:
Originally posted by awebb

Hi, How do I change the data in a select column from a number to a specific character. ie I need number 1 to change to a and number 2 to change to b.

thanks,

What if there is a number 983745?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 02:19:40

quote:
Originally posted by madhivanan

quote:
Originally posted by awebb

Hi, How do I change the data in a select column from a number to a specific character. ie I need number 1 to change to a and number 2 to change to b.

thanks,

What if there is a number 983745?

Madhivanan

Failing to plan is Planning to fail

may be what he want is ihcgde

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-17 : 03:55:55

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

quote:
Originally posted by awebb

Hi, How do I change the data in a select column from a number to a specific character. ie I need number 1 to change to a and number 2 to change to b.

thanks,

What if there is a number 983745?

Madhivanan

Failing to plan is Planning to fail

may be what he want is [b]ihcgde[/i]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I think you meant ihcgde

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 04:00:43

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

quote:
Originally posted by awebb

Hi, How do I change the data in a select column from a number to a specific character. ie I need number 1 to change to a and number 2 to change to b.

thanks,

What if there is a number 983745?

Madhivanan

Failing to plan is Planning to fail

may be what he want is [b]ihcgde[/i]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I think you meant ihcgde

Madhivanan

Failing to plan is Planning to fail

exactly..sorry i messed up the formatting tags

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources