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

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:

I want to display it as this:

Thanks

CodePudding user response：

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