I am trying to split the below string using OPENJSON function in SQL Server 2019:
x
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