November 9, 2009 at 1:58 pm
Data in the table
id parent
1 1/2/3
2 2/3/4
what i am trying to get is 1=Father 2=Mother 3=Sister 4=Mother
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.
November 9, 2009 at 2:04 pm
create table #T (
ID int identity primary key,
Members varchar(100));
insert into #T (Members)
select '1/2/3' union all
select '2/3/4';
select ID,
case Parsed
when 1 then 'Father'
when 2 then 'Mother'
when 3 then 'Sister'
when 4 then 'Brother'
end as Relation
from #T
cross apply
(select substring(Members + '/', Number, charindex('/', Members + '/', Number) - Number) Parsed
from dbo.Numbers
where Number <= len(Members)
and substring('/' + Members, Number, 1) = '/') Parser;
You need a Numbers table for this. If you don't have one, create one:
create table dbo.Numbers (Number int primary key);
go
insert into dbo.Numbers (Number)
select top 10000 row_number() over (order by t1.object_id)
from sys.columns t1
cross join sys.columns t2;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply