SQL Server 2019中拆分两列逗号分隔字符串并保持位置对应

这个痛点我太熟悉了——STRING_SPLIT单独用没问题，但同时拆两列时顺序乱掉，核心原因就是它默认不返回子串在原字符串中的位置序号。好在SQL Server 2019刚好给STRING_SPLIT加了个关键参数，完美解决这个对齐问题！

核心思路 #

SQL Server 2019引入了STRING_SPLIT的第三个参数enable_ordinal，当设为1时，函数会额外返回一个ordinal列，代表每个子串在原字符串中的位置（从1开始计数）。我们只要用这个序号+原表的唯一行标识（比如主键）来关联两列的拆分结果，就能保证对应位置的内容严格匹配。

具体实现代码 #

假设你的原表叫original_table，有唯一标识每行的列（比如row_id），需要拆分的两列是column1和column2，直接用CTE拆分后关联即可：

-- 用CTE分别拆分两列并保留位置序号
WITH split_col1 AS (
    SELECT
        row_id,
        TRIM(value) AS col1_value, -- 顺便去除子串前后可能的空格
        ordinal
    FROM original_table
    CROSS APPLY STRING_SPLIT(column1, ',', 1) -- 第三个参数1开启序号返回
),
split_col2 AS (
    SELECT
        row_id,
        TRIM(value) AS col2_value,
        ordinal
    FROM original_table
    CROSS APPLY STRING_SPLIT(column2, ',', 1)
)
-- 通过row_id和ordinal关联，确保位置完全对应
SELECT
    sc1.row_id,
    sc1.col1_value,
    sc2.col2_value
FROM split_col1 sc1
JOIN split_col2 sc2
    ON sc1.row_id = sc2.row_id
    AND sc1.ordinal = sc2.ordinal;

如果需要把拆分结果存入新表（你提到可以新建表），只需在查询末尾加INTO语句：

WITH split_col1 AS (
    SELECT
        row_id,
        TRIM(value) AS col1_value,
        ordinal
    FROM original_table
    CROSS APPLY STRING_SPLIT(column1, ',', 1)
),
split_col2 AS (
    SELECT
        row_id,
        TRIM(value) AS col2_value,
        ordinal
    FROM original_table
    CROSS APPLY STRING_SPLIT(column2, ',', 1)
)
SELECT
    sc1.row_id,
    sc1.col1_value,
    sc2.col2_value
INTO new_split_table -- 新建表存储拆分后的结果
FROM split_col1 sc1
JOIN split_col2 sc2
    ON sc1.row_id = sc2.row_id
    AND sc1.ordinal = sc2.ordinal;

额外注意事项 #

• 确认你的SQL Server 2019版本支持enable_ordinal参数（这个是2019及以后版本才有的特性）。
• 如果两列的逗号分隔子串数量不一致，上面的JOIN会只保留序号匹配的行。如果需要保留所有行（哪怕某一列对应位置无值），可以改用FULL JOIN，并用COALESCE处理空值：

  SELECT
      COALESCE(sc1.row_id, sc2.row_id) AS row_id,
      sc1.col1_value,
      sc2.col2_value
  FROM split_col1 sc1
  FULL JOIN split_col2 sc2
      ON sc1.row_id = sc2.row_id
      AND sc1.ordinal = sc2.ordinal;
  

• 如果子串本身包含逗号（比如带引号的嵌套内容），STRING_SPLIT会直接拆分，这种情况需要额外处理，但你提到数据是机器自动导入的，应该不会有这类问题。

内容的提问来源于stack exchange，提问作者creedcode