Using T-SQL, I have a comma delimited string like this

'Value1,Value2,"Value3,Value4,Value5",Value6'

and I want to put it into an array like this:

Array[0] = 'Value1'
Array[1] = 'Value2'
Array[2] = 'Value3,Value4,Value5'
Array[3] = 'Value6'

Any help is appreciated! Thanks!

CodePudding user response：

This is a bit convulted method.
But it can transform the string in the format of a JSON array.
Then it'll be easier to use JSON functions on it.

The example uses a UDF fnPattern_Split that accepts a pattern to unnest the string.
The source code can be found here

declare @str varchar(max), @js varchar(max);
set @str ='Value1,Value2,"Value3,Value4,Value5",Value6';

;with cte1 as (
select *
from dbo.fnPattern_Split(@str,'"%"') ps
) 
, cte2 as (
  select ordinal as ord1, 0 as ord2, value 
  from cte1 
  where match = 1
  union all
  select c.ordinal, ps.ordinal, quotename(ps.value,'"') as value 
  from cte1 c
  cross apply fnPattern_Split(c.value,',') ps
  where c.match = 0 and ps.match = 0
) 
select @js = '[' string_agg(value, ',')
                 within group (order by ord1, ord2) ']'
from cte2;

print(@js);

select *
from OPENJSON(@js) js;

Test on db<>fiddle here

CodePudding user response：

string_split is the function you are looking for but it does not support quotes.

select * from string_split('Value1,Value2,Value3,Value6', ',')