Need to get a table from a string value in SQL

Govind Sankar M R 20 Reputation points
2023-05-16T11:47:12.07+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,078 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Yitzhak Khabinsky 25,381 Reputation points
    2023-05-16T12:52:59.5833333+00:00

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

  2. LiHongMSFT-4306 24,361 Reputation points
    2023-05-17T05:55:45.27+00:00

    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.

    0 comments No comments