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


Scripts are tested in Microsoft Azure VM - SQL Server 2016 CTP 3.2
Reference:
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:
Reference:
No comments:
Post a Comment