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... — Gottfried Lesigang
– Gottfried Lesigang, Commented Oct 24, 2018 at 7:39

userfl89 · Accepted Answer · 2018-10-23 14:58:04Z

2

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)

answered Oct 23, 2018 at 14:58

userfl89

4,8201 gold badge13 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SQL006 Over a year ago

Thanks for the solution.

KeithL Over a year ago

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 · Accepted Answer · 2018-10-23 15:19:43Z

1

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

answered Oct 23, 2018 at 15:19

KeithL

5,6193 gold badges23 silver badges30 bronze badges

Comments

Gottfried Lesigang · Accepted Answer · 2018-10-24 07:38:37Z

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

Collectives™ on Stack Overflow

Split the string using String_Split() in SQL Server 2016

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related