CODEWITHSUNDEEP
jsonsql-server
Sandeep540
Sandeep540

Reputation: 977

SQL Server Split delimited String to JSON Array

Server : SQL Server 2016

I am trying to split a delimited string in one column in DB to JSON Array for easy joins in ETL Job

shirt|~*~|trouser|~*~|t-Shirt|~*~|towels|~*~|

into JSON Value

   {"values":["shirt", "trouser", "t-Shirt", "towels"]}

One of the article I followed here helped a little, but could not get me far enough, I want to convert using select instead of procedure/function

Any Pointers is helpful

SQL to JSON - array of objects to array of values in SQL 2016

Upvotes: 1

Views: 2222

Answers (1)

Thom A
Thom A

Reputation: 95544

Seems like REPLACE would be better option here:

DECLARE @CSV varchar(MAX) = 'shirt|~*~|trouser|~*~|t-Shirt|~*~|towels|~*~|';

SELECT '{"values":["' + REPLACE(V.csv,'|~*~|','","') + '"]}'
FROM (VALUES(@CSV))V(csv);

Upvotes: 1

Related Questions