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
 split array

Author  Topic 

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-02-21 : 08:11:36
hi,

how can i split text separated by semicolumn in different cells:

text1;text2;text3;
into
1 - text1
2 - text2
3 - text3

thank you

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-21 : 08:12:41
Search for Split function here

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-02-21 : 20:52:00
Here's yet another way I wrote recently. I kind of like it because there is no function to create so you can use it in situations when you can't modify the database.
Of course doing this stuff sucks - fix up your data model if you can.


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 number p from master..spt_values where type='p' and number<=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
)

/*
Your query goes here.
With the CTE you have access to a table called 'chunks' which has a column called 'chunk'
*/

select * from blah
where wibble in(
select
chunk
from chunks
)


Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-21 : 20:56:11
or if you are lazy to write one yourself just use one of the below
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648


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

Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-02-21 : 21:04:45
Just realised this is limited to strings of 2045 chars. This version will go further:


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

- Advertisement -