Converting CSV string to table using recursive query

How to convert a comma delimited string into a list? Here is one example of how to do it using recursive query:

declare @string Table (string varchar(200))

insert
into @string values(‘a,bb,c,ddd,eeee,fff’);

with split as

(

select
SUBSTRING(string, 0, CHARINDEX
(‘,’,string + ‘,’))
as chars    

,0 as startpos

,
CHARINDEX
(‘,’,string + ‘,’)
as endpos

from
@string

union all

select
SUBSTRING(string,endpos +1,
CHARINDEX(‘,’, string +
‘,’, split.endpos+1)
endpos 1) as chars    

,split.endpos +1 as startpos

,
CHARINDEX(‘,’, string +
‘,’, split.endpos+1) as endpos

from

split cross
join @string

where endpos<
LEN(string)

)   
select chars from split;

Note: To be able to support multiple strings, this query needs to be modified. Cross join will not work. 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s