Replace Number with Text - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Replace Number with Text

Author

Topic

SpeshulK926
Starting Member

16 Posts

Posted - 2007-05-08 : 14:02:08

I have a stored procedure SELECT statement that will return 3 different numbers, "1, 2, and 3". I need to put in some static text instead of the numbers, so it will look like this...

SELECT FieldName FROM TableName

IF FieldName = '1' THEN = 'Text1',
IF FieldName = '2' THEN = 'Text2',
IF FieldName = '3' THEN = 'Text3'

I know that is now how you do it, but close as I could get to knowing what I want to do... Anyone can help?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-08 : 14:07:15

You want to check out the CASE statment in sql.


select 
  case FieldName
  when 1 then 'text1'
  when 2 then 'text2'
  when 3 then 'text3'
  end
from yourtable

www.elsasoft.org

SpeshulK926
Starting Member

16 Posts

Posted - 2007-05-08 : 14:16:52

quote:
Originally posted by jezemine

You want to check out the CASE statment in sql.

select
case FieldName
when 1 then 'text1'
when 2 then 'text2'
when 3 then 'text3'
end
from yourtable

www.elsasoft.org

Worked great! 1 more question, how do I name the table? I tried

case FieldName
when 1 then 'text1' as FieldNameIWant
when 2 then 'text2' as FieldNameIWant
when 3 then 'text3' as FieldNameIWant
end

also tried

case FieldName as FieldNameIWant
when 1 then 'text1'
when 2 then 'text2'
when 3 then 'text3'

Both threw an exception...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-08 : 14:24:44

case FieldName
when 1 then 'text1'
when 2 then 'text2'
when 3 then 'text3'
end as FieldNameIWant

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-08 : 15:13:25

Read up on how CASE works, and this might also be helpful:

http://weblogs.sqlteam.com/jeffs/archive/2007/05/03/60195.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-09 : 03:15:47

quote:
Originally posted by SpeshulK926

I have a stored procedure SELECT statement that will return 3 different numbers, "1, 2, and 3". I need to put in some static text instead of the numbers, so it will look like this...

SELECT FieldName FROM TableName

IF FieldName = '1' THEN = 'Text1',
IF FieldName = '2' THEN = 'Text2',
IF FieldName = '3' THEN = 'Text3'

I know that is now how you do it, but close as I could get to knowing what I want to do... Anyone can help?

Just a thought
If you put static text with column values, you can use

Select 'static_text'+cast(col as varchar(10)) from table

Though this seems presentation issue

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources