代码之家 › 专栏 › 技术社区 › SQL006

在SQL Server 2016中使用string\ u Split()拆分字符串

sql-server-2016 delimiter split ssis tsql

3

SQL006 · 技术社区 · 6 年前

我需要使用 STRING_SPLIT 并将结果导入另一个表中。

阶段表:

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'

最终表格:

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

我使用SSIS execute task转换stage表数据并插入到最终的表中。stage表中的Code列是string,“\”用作分隔符。我需要分离字符串并显示最终的表,如下所示

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

我正在尝试使用SQL Server 2016内置 String_Split() 功能如下:

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

但它一次只拆分一个字符串,因为我的stage数据包含数百万条记录,所以我需要拆分code列中的所有字符串并存储在相应的列中。

谢谢

3 回复 | 直到 6 年前

1

2

userfl89 6 年前

您可以添加一个派生列,并假设格式与您列出的格式一致,使用 TOKEN 函数根据 "_" 分隔符和每个字符串的位置。从这里,您可以将每个输出映射到相应的目标列。下面的三条语句根据问题中的示例数据拆分代码列。注意,TOKEN的输出数据类型是 DT_WSTR (Unicode)。如果需要非Unicode数据,则必须将其转换回 DT_STR (DT_STR,50,1252) (根据需要调整长度)。

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

2

1

KeithL 6 年前

与@userfl89类似,这里有另一个使用脚本组件的SSIS解决方案: 将4个输出列添加到output0。确保在“输入”列中选择“代码为”。

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

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

3

0

Gottfried Lesigang 6 年前

既然公认的答案是 TOKEN() ,它绑定到SSIS,我也想提供一个SQL Server解决方案。

您使用的是v2016,这允许 OPENJSON . 当您在JSON数组中使用它时,您将得到一列[key],指示数组中的位置,以及一列[value],提供实际内容。

将CSV字符串转换为JSON数组非常容易。其余的都是旋转的 条件聚合 . 试一试:

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