Tuesday, June 2, 2015

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

4 comments:

  1. Thanks for sharing Clark...
    Cheers....

    ReplyDelete
  2. Thanks for sharing. I like the way you write your code would it be possible to give some explanation to your work. I am new in sql and have a difficult time to understand. I test it and it works perfect

    ReplyDelete
  3. BEWARE, this sql is buggy. It cant handle elements that have other elements within like : '1,2,3,5,6,9,10,11'

    ReplyDelete
  4. Thanks for reporting the bug. I fix the code to handle the issue.

    ReplyDelete