SQL Server - Split CSV into separate row and other splitting techniques using OPENJSON

In SQL Server 2016, Microsoft provides built-in support for importing, exporting, parsing, and querying JSON documents or fragments.

In this article, let us see some examples on splitting comma separated string into separate rows using OPENJSON

 --Split comma separated numeric
SELECT value FROM OPENJSON('[1,2,3]')
-- Split comma separated alphabets
SELECT value FROM OPENJSON('["A","B","C"]')
--Ignore null while splitting

SELECT value FROM OPENJSON('[1,"B",3,"D",null]')
WHERE type <> 0
-- Get value from particular position from comma separated string
SELECT value FROM OPENJSON('[1,"B",3,"D"]')
WHERE [key] = 1 --( Position - 1 )
--Does not split comma separated string enclosed within double quotes
SELECT value FROM OPENJSON('["MS,SQL"]')

 

To better understand on how OPENJSON parses comma separated values:

SELECT *
FROM OPENJSON(
'[
null,
"string",
1,
true,
false,
["a","r","r","a","y"],
{"obj":"ect"}]')

Scripts are tested in Microsoft Azure VM - SQL Server 2016 CTP 3.2 

 

See Also:

•  My posts on SQL Server JSON

 Reference:

•   Passing arrays to T-SQL procedures as JSON