Convert Delimited String into Rows

Here's another way of converting a delimited string into a result set or rows. This technique is much more straightforward and does not involve xml processing. It saves memory, specially if this step needs to be executed inside a loop.

Say you have this string:

x
----------------------------------------------------------
one, two, three, four, five, six, seven, eight, nine, ten

Which needs to be converted into:

xRow
--------
one
two
three
four
five
six
seven
eight
nine
ten

Here's the code...

First,  all spaces in the string must be removed and an extra delimiter needs to be added in the end of the string.

declare @x as varchar(150)

set @x = 'one, two, three, four, five, six, seven, eight, nine, ten'

set @x = replace(@x, ' ','') + ','

select @x as x

Second, using CTE, the string can be parse using the delimiter to identify where to break...

;with ToTable
as
(
   select cast(substring(@x,1, charindex(',',@x)-1) as varchar(150)) as xRow, cast(substring(@x,charindex(',',@x)+1, 150) as varchar(150)) as xValue2
   union all
   select cast(substring(xValue2,1, charindex(',',xValue2)-1) as varchar(150)) as xValues,  cast(substring(xValue2,charindex(',',xValue2)+1, 150) as varchar(150)) as xValue2
   from ToTable where xValue2 <> ''
)
select xRow
from ToTable

Now that it's a result set, it can be used to JOIN with other tables. Consideration should always be done how big the tables are.

If ever there's a need to use variable values inside an IN clause, this technique is very useful. Check this out on how to do this through XML.

As always, comments are welcome...


~~ CK