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
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgiN5SWeCiwb-nTjLMD-DKl-Ot_OW_W4F3lS7C1xQnHQduJM-fEpTVRcBJNNfJkdNIxVuXfLZs-c2eYtZkEWb4PLUH4UBq3zh8SJIWD69AcgHAnbqUNmTAQr4RZresWLtgAfb5h0p6UR_TQ/s640/j5.png)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg14V2wRB7Wma_sIC3vNI04lJjeeDplx4rP5InOXZOuI7hIDk_m0eAyCd36Y9MORgr0b9wkWYrVcw7usikAIueTNgdd1dtQedoqmFxnhzmufIk8pqpMQ-xmXbl_8tmaXz__nY2RpaGi3Lxz/s640/j6.png)
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"]')
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgiN5SWeCiwb-nTjLMD-DKl-Ot_OW_W4F3lS7C1xQnHQduJM-fEpTVRcBJNNfJkdNIxVuXfLZs-c2eYtZkEWb4PLUH4UBq3zh8SJIWD69AcgHAnbqUNmTAQr4RZresWLtgAfb5h0p6UR_TQ/s640/j5.png)
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"}]')
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg14V2wRB7Wma_sIC3vNI04lJjeeDplx4rP5InOXZOuI7hIDk_m0eAyCd36Y9MORgr0b9wkWYrVcw7usikAIueTNgdd1dtQedoqmFxnhzmufIk8pqpMQ-xmXbl_8tmaXz__nY2RpaGi3Lxz/s640/j6.png)
Scripts are tested in Microsoft Azure VM - SQL Server 2016 CTP 3.2
See Also:
Reference:
No comments:
Post a Comment