Need to get a table from a string value in SQL

Question

So I have this one column value which is a long string. Lets say the columns name is Column_header. And the value it has is ["Value1","Value2","Value3","Value4","Value5"]. I need to turn this into a table with each value as Value1,Value2 etc. So first tried String Split with " as the string used to split. But then I got the solution as [ Value1 , Value2 etc (comma is a value in the new table). So i realised i have to remove the [ ] and all commas. But for that I have been trying different things like trim, replace etc. But I cannot do everything together. That is remove all [] and comma and then create array from solution. I have been googling for a long time now and I give up. So what is the solution for this. Kindly do let me know. Thank you.

Answer 1

Hi @Govind Sankar M R,

Please try the following solution. It will work starting from SQL Server 2017 onwards.

-- DDL and data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, tokens NVARCHAR(MAX));
INSERT @tbl (tokens) VALUES
('["Value1","Value2","Value3","Value4","Value5"]'),
('[Value1,Value2,Value3]');
-- DDL and data population, end

SELECT * FROM @tbl;

SELECT ID, value
FROM @tbl
CROSS APPLY (SELECT tokens = IIF(LEFT(tokens,2) <> '["',
	'["' + TRIM('[]' FROM REPLACE(tokens, ',', '","')) + '"]', tokens)) AS t1(j)
CROSS APPLY OPENJSON(j);

Answer 2

Hi @Govind Sankar M R

I have been trying different things like trim, replace etc. But I cannot do everything together.

Try this:

DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, tokens NVARCHAR(MAX));
INSERT @tbl (tokens) VALUES
('["Value1","Value2","Value3","Value4","Value5"]'),
('[Value1,Value2,Value3]');

SELECT ID,C.value FROM @tbl CROSS APPLY STRING_SPLIT (TRIM('[]' FROM REPLACE(tokens,'"','')),',')C

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.