1

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

Sandeep540
  • 897
  • 3
  • 13
  • 38
  • 2
    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 Sep 05 '19 at 15:39
  • We are reading the Data from SQL Views in Batch Jobs as a part of ETL – Sandeep540 Sep 05 '19 at 15:45
  • 3
    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 Sep 05 '19 at 15:46
  • Why is there a delimiter at the end? Shouldn't it therefore be `'{"values":["shirt", "trouser", "t-Shirt", "towels",""]}'`? – Thom A Sep 05 '19 at 15:57
  • The Delimited string is a part of DB which legacy, I cannot get the weird delimited string into Master DB as its of no use, at least with JSON array, I can cross join – Sandeep540 Sep 05 '19 at 15:57
  • 2
    I think "step back and question your pipeline" should be the accepted answer. – McGlothlin Sep 05 '19 at 16:01

1 Answers1

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);
Thom A
  • 88,727
  • 11
  • 45
  • 75