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
 General SQL Server Forums
 New to SQL Server Programming
 How to split a comma in a string

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 display

abc
def
ghi

I have to separate a comma in that
Is there an function like split in sql

plz help me
Thanks in Advance

Suresh Kumar

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-11 : 08:33:58
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

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 @textXML
SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @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'
Go to Top of Page

soori457
Yak Posting Veteran

85 Posts

Posted - 2008-03-11 : 08:52:39
Thanks for ur reply Mr.Ranganath

Can u explain me, why we need xml there

Suresh Kumar
Go to Top of Page

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"
Go to Top of Page

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
) z
where 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 s
where start<len(@csv)+2
)

select * from chunks

I'd fix up your parameter to pass something useful though
Go to Top of Page
   

- Advertisement -