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
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