Author |
Topic |
soori457
Yak Posting Veteran
85 Posts |
Posted - 2008-03-11 : 08:32:27
|
I have a string @string = 'abc,def,ghi'Now I have to displayabcdefghiI have to separate a comma in thatIs there an function like split in sqlplz help meThanks in AdvanceSuresh Kumar |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
ranganath
Posting Yak Master
209 Posts |
Posted - 2008-03-11 : 08:37:18
|
Hi,Try with this DECLARE @data NVARCHAR(MAX), @delimiter NVARCHAR(5)SELECT @data = '1,2,3,6,7,8,R,a,n,g,a,n,a,t,h', @delimiter = ','DECLARE @textXML XML;SELECT @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML); SELECT @textXMLSELECT T.split.value('.', 'nvarchar(max)') AS dataFROM @textXML.nodes('/d') T (split)it will work on Sql2005 only(or)DEclare @s Varchar(100) , @Q varchar(1000)Set @s = '1,2,3,4,5,6,a,s,d,f'Set @Q = Replace(@s, ',','')SELECT SUBSTRING ( @Q, number+1,1) AS OrderID FROM Master..spt_Values n where n.Number < DataLength(@Q) and type = 'P' |
|
|
soori457
Yak Posting Veteran
85 Posts |
Posted - 2008-03-11 : 08:52:39
|
Thanks for ur reply Mr.RanganathCan u explain me, why we need xml thereSuresh Kumar |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-03-11 : 09:26:19
|
Ranganaths second suggestion will only work with single digit items, and not per as OP requested in orignal post. E 12°55'05.25"N 56°04'39.16" |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-03-11 : 20:38:33
|
DECLARE @csv VARCHAR(255) SET @csv = 'gvfgmgm,f,a,cls,pcl,r,o,fhhh,h' ;with s(start) as (SELECT distinct charindex(',',','+@csv+',',p)FROM (select * from(select row_number() over (order by m1.number) p from master..spt_values m1,master..spt_values m2) zwhere p <=len(@csv)+2) x ),chunks(chunk) as (select substring(@csv,start,(select min(start) from s as s2 where s2.start>s.start)-start-1)from swhere start<len(@csv)+2)select * from chunksI'd fix up your parameter to pass something useful though |
|
|
|
|
|