Help With Case - 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)

Help With Case

Author

Topic

wjohnson16
Starting Member

5 Posts

Posted - 2009-11-09 : 16:32:57

Data in the table

Both Data Type nvarhcar

id parent
1 1/2/3
2 2/3/4

what i am trying to get is 1=Father 2=Mother 3=Sister 4=Brother

1 Father/Mother/Sister
2 Mother/Sister/Brother

select id, case Members
when 1 then 'Father'
when 2 then 'Mother'
end
from test

Error Conversion failed when converting the nvarchar value '1\2\3' to data type int.

mfemenel
Professor Frink

1421 Posts

Posted - 2009-11-09 : 17:08:47

Cast your numbers to char. for example when cast(1 as varchar(20)) then 'Father'.
The problem is sql treats the values it finds in your case statement as the first data type it finds. So it sees 1 as an int and expects our then clause to provide it an int as well. It hits 'Father' and melts down. Casting to a varchar will get you around this.

Mike
"oh, that monkey is going to pay"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-10 : 03:16:21

declare @t table(id int, parent varchar(100))
insert into @t
select 1, '1/2/3' union all
select 2, '2/3/4'

select id,parent,substring(new_parent,2,len(new_parent)-2) as new_parent from
(
select id,parent,replace(replace(replace(replace('/'+parent+'/','/1/','/Father/'),'/2/','/Mother/'),'/3/','/Sister/'),'/4/','/Brother/') as new_parent
from @t
) as t

Madhivanan

Failing to plan is Planning to fail

wjohnson16
Starting Member

5 Posts

Posted - 2009-11-13 : 15:16:47

Thanks for you help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-16 : 01:33:48