Split the string using String_Split() in SQL Server 2016

Question

I need to use STRING_SPLIT in my stage table and import the results into another table.

Stage table:

DECLARE @stage TABLE(ID INT, Code VARCHAR(500))

INSERT INTO @stage
    SELECT 1, '123_Potato_Orange_Fish' 
    UNION ALL
    SELECT 2, '456_Tomato_Banana_Chicken' 
    UNION ALL
    SELECT 3, '789_Onion_Mango_Lamb'

Final table:

DECLARE @Final TABLE
               (
                    ID INT, 
                    code VARCHAR(500),
                    Unit VARCHAR(100),
                    Vegetable VARCHAR(100),
                    Fruit VARCHAR(100),
                    Meat VARCHAR(100)
               )

I am using SSIS execute task to transform the stage table data and insert into the final table. The Code column in stage table is string and '_' is used for delimiter. I need to separate the string and display the final table as shown below

ID        code                  Unit    Vegetable   Fruit   Meat
------------------------------------------------------------------
1   123_Potato_Orange_Fish      123      Potato    Orange   Fish
2   456_Tomato_Banana_Chicken   456      Tomato    Banana   Chicken
3   789_Onion_Mango_Lamb        789      Onion     Mango    Lamb

I am trying to use SQL Server 2016 built-in String_Split() function as shown here:

SELECT 
    ID,
    Code, f.value AS Vegetable 
FROM 
    @stage AS s
CROSS APPLY
    (SELECT 
         value,
         ROW_NUMBER() OVER(PARTITION BY s.ID ORDER BY s.ID) AS rn 
     FROM
         String_Split(s.Code, '_')) AS f
WHERE 
    s.ID = 1 AND f.rn = 2

But it only split one string at a time, as my stage data contain millions of records i need to split all the string in the code column and store in the respective column.

Note: I don't want to use temporary table.

thanks

`STRING_SPLIT()` - why ever - will not guarantee to return the fragments in the given order. But there is `OPENJSON`, see my answer... — Shnugo, Oct 24 '18 at 07:39

score 2 · Accepted Answer · answered Oct 23 '18 at 14:58

You can add a Derived Column and assuming that the format is consist with what you listed, use the TOKEN function to split the input based on the "_" delimiter and position of each string. From here, you can map each of the outputs to the appropriate destination column. The three statements below split your code column based on the sample data in your question. Note that the output data type of TOKEN is DT_WSTR (Unicode). If you need the non-Unicode data, you'll have to cast it back to DT_STR, which can also be done within the same Derived Column by adding (DT_STR,50,1252) (adjust length as necessary) before each statement.

TOKEN(Code,"_",1)
TOKEN(Code,"_",2)
TOKEN(Code,"_",3)

This was news to me. I guess I have been doing it the other way for so long I didn't bother to learn — KeithL, Oct 23 '18 at 18:18

score 1 · Answer 2 · answered Oct 23 '18 at 15:19

Like @userfl89 here is another SSIS solution using script component: Add the 4 output columns to your output0. Make sure you select Code as in input column.

string[] col = Row.Code.ToString().Split('_');

Row.Unit = Int.Parse(col[0]);
Row.Vegetable = col[1];
Row.Fruit = col[2];
Row.Meat = col[3];

score 0 · Answer 3 · answered Oct 24 '18 at 07:38

Since the accepted answer uses TOKEN(), which is bound to SSIS, I want to provide a SQL-Server-solution too.

You are using v2016, that allows for OPENJSON. When you use this on a JSON-array you'll get a column [key] indicating the position in the array and a column [value] providing the actual content.

It is very easy to transform a CSV-string to a JSON array. The rest ist pivoting by conditional aggregation. Try it out:

DECLARE @stage TABLE(ID INT, Code VARCHAR(500))

INSERT INTO @stage
    SELECT 1, '123_Potato_Orange_Fish' 
    UNION ALL
    SELECT 2, '456_Tomato_Banana_Chicken' 
    UNION ALL
    SELECT 3, '789_Onion_Mango_Lamb' 

SELECT ID 
      ,Code
      ,MAX(CASE WHEN [key]=0 THEN CAST([value] AS INT) END) AS Unit
      ,MAX(CASE WHEN [key]=1 THEN [value] END) AS Vegetable
      ,MAX(CASE WHEN [key]=2 THEN [value] END) AS Fruit
      ,MAX(CASE WHEN [key]=3 THEN [value] END) AS Meat
FROM @stage
CROSS APPLY OPENJSON('["' + REPLACE(Code,'_','","') + '"]') A
GROUP BY ID,Code

Split the string using String_Split() in SQL Server 2016

3 Answers3