Split a column in T-SQL-CodePudding

I'm new to using T-SQL, how do you separate an NVARCHAR column with the following information

[3293,"Maria","CA","Auto"]
[67093,"Joana","WA","Manual"]

I would like to get 4 columns like this

col1   col2    col3  col4
3293   Maria   CA    Auto
67093  Joana   WA    Manual

Thanks

CodePudding user response：

You can use openjson and aggregate:

select
 max(case when [key] = 0 then value end) col1,
 max(case when [key] = 1 then value end) col2,
 max(case when [key] = 2 then value end) col3,
 max(case when [key] = 3 then value end) col4
from OpenJson('[3293,"Maria","CA","Auto"]')

CodePudding user response：

Without the need for an aggregation.

Example

Select Col1 = JSON_VALUE([SomeCol],'$[0]')
      ,Col2 = JSON_VALUE([SomeCol],'$[1]')
      ,Col3 = JSON_VALUE([SomeCol],'$[2]')
      ,Col4 = JSON_VALUE([SomeCol],'$[3]')
 From YourTable A

Results

Col1    Col2    Col3    Col4
3293    Maria   CA      Auto
67093   Joana   WA      Manual

CodePudding user response：

Use 'OpenJson' and pass the column values in a loop.

--Step1: Create a Temporary table and add Row_Number

select ROW_NUMBER() over( order by COL) as r,* 
INTO #Temp_table
from YourTable;

--Step2: Declare and set Variables

DECLARE @count INT;
DECLARE @row INT;
DECLARE @JSON NVARCHAR(250);
set @count= (select COUNT(1) FROM #Temp_table);
SET @row = 1;

--Step3: Create Final table (Here, using temp final table)
CREATE TABLE #TEMP_FINAL 
(COL1 INT,COL2 VARCHAR(100),COL3 VARCHAR(100),COL4 VARCHAR(100));

--Step4: Iterate over loop

WHILE (@row <= @count) BEGIN
    
    SELECT @JSON=COL FROM #Temp_table WHERE @row=r;
    
    INSERT INTO #TEMP_FINAL
    select
    max(case when [key] = 0 then value end) col1,
    max(case when [key] = 1 then value end) col2,
    max(case when [key] = 2 then value end) col3,
    max(case when [key] = 3 then value end) col4
    from OpenJson(@JSON);
    
    SET @row  = 1;
END 

--Step5: Select the values

SELECT * FROM #TEMP_FINAL

To have understanding, you can review following links

Row_Number: https://www.sqlservertutorial.net/sql-server-window-functions/sql-server-row_number-function/ While Loop: https://www.sqlshack.com/sql-while-loop-with-simple-examples/