Skip to content
Advertisement

How to split a string which contains delimiter into multiple rows using OPENJSON function

I am trying to split the below string using OPENJSON function in SQL Server 2019:

DECLARE @x varchar(400)  = N'A,B,"C,1",D,'

--SELECT '["'+replace(string_escape(@x,'json'),',','","')+'"]'

SELECT 
    [Key]+1 AS Seq,
    Value 
FROM 
    OPENJSON('["' + REPLACE(STRING_ESCAPE(@x, 'json'), ',', '","') + '"]')

But the result is returned as:

enter image description here

I want to display it as this:

enter image description here

Thanks

Advertisement

Answer

This is where my thinking brings me.

Note: Unexpected results of the "'s are NOT balanced.

Example or dbFiddle

DECLARE @x varchar(400)  = N'A,B,"C,1",D,'

Select Seq= Grp
      ,value = replace(string_agg(Value,','),'"','')
 From (
        SELECT [Key]+1 AS Seq
              ,Value 
              ,Grp = sum( case when charindex('"',Value)<=1 then 1 else 0 end ) over (order by [key]+0) 
        FROM OPENJSON('["'+replace(string_escape(@x,'json'),',','","')+'"]')
      ) A
 Group By Grp
 Order By Grp

Results

Seq value
1   A
2   B
3   C,1
4   D
5   
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement