Splitting variable length delimited string across multiple rows (SQL)

Question

I have a table that contains in one column a variable length delimited string for example:

20,0, 5,,^24,0, 0,,^26,0, 0,,^
281,0, 0,,^34,0, 2,,^48,0, 2,,^44,0, 2,,^20,0, 10,,^
20,5, 5,,^379,1, 1,,^26,1, 2,,^32,0, 1,,^71,0, 2,,^

What i need to do is split this string so that each number after the ^ character is returned on a new row. like:

Item Number Item Code
Item1 20
Item2 ^24
Item3 ^24
Item4 ^27
Item5 ^28
Item6 ^65
Item7 ^66
Item8 ^39
Item9 ^379
Item10 ^448
Item11 ^427

I've tried various split functions and I can manage to achieve the result i need by substring'ing the values across multiple columns and then using unpivot to return them across multiple rows however this method doesnt handle teh variable length of this string.

Any ideas of a better approach?

Answer 1

First, let me just say that this is the reason that you shouln't have comma separated data in a field in the first place. There is no easy or efficient way to work with it.

That said, you can use a recursive query to split the string and get the numbers from it:

with split as
(
  select
    item = cast('' as varchar(max)),
    source = cast('20,0, 5,,^24,0, 0,,^26,0, 0,,^281,0, 0,,^34,0, 2,,^48,0, 2,,^44,0, 2,,^20,0, 10,,^20,5, 5,,^379,1, 1,,^26,1, 2,,^32,0, 1,,^71,0, 2,,^' as varchar(max))
  union all
  select
    item = substring(source, 1, charindex(',,', source)),
    source = substring(source, charindex(',,', source) + 2, 10000)
  from split
  where source > ''
)
select substring(item, 1, charindex(',', item) -1)
from split
where item > ''

Result:

20
^24
^26
^281
^34
^48
^44
^20
^20
^379
^26
^32
^71