SQL Server Split delimited String to JSON Array

Question

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

Do you have to do this in SQL Server? I mean, it would be much easier to handle unnormalized CSV data somewhere like C# or Java than in a database. — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 5, 2019 at 15:39
We are reading the Data from SQL Views in Batch Jobs as a part of ETL — Sandeep540
– Sandeep540, Commented Sep 5, 2019 at 15:45
OK, any time you find yourself pulling in large amounts of CSV data into your database, it might be time to step back and question your pipeline. — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 5, 2019 at 15:46
Why is there a delimiter at the end? Shouldn't it therefore be '{"values":["shirt", "trouser", "t-Shirt", "towels",""]}'? — Thom A
– Thom A ♦, Commented Sep 5, 2019 at 15:57
I think "step back and question your pipeline" should be the accepted answer. — McGlothlin
– McGlothlin, Commented Sep 5, 2019 at 16:01

Thom A · Accepted Answer · 2019-09-05 15:58:45Z

1

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);

answered Sep 5, 2019 at 15:58

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server Split delimited String to JSON Array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related