如何使用T-SQL将含竖线分隔拼接字符串的列拆分为多行？

针对你遇到的这种需要重复处理竖线分隔列、还要适配未来未知表的场景，我整理了两种成熟的可复用方案，分别对应T-SQL和R环境，完美覆盖你的需求：

T-SQL 方案（适配SQL Server环境） #

如果你主要在SQL Server里处理数据，以下方案可以直接在数据库中复用，不需要额外工具：

1. 基础拆分写法（SQL Server 2016+） #

用SQL Server 2016开始内置的STRING_SPLIT函数配合CROSS APPLY，这是最简洁的方式，自动处理任意数量的拆分值（不管是0个还是60个都没问题）：

-- 示例：拆分现有表的指定列
SELECT 
    -- 保留原表除拆分列外的所有列
    t.* EXCEPT (要拆分的列名),
    -- 拆分后的单个值
    s.value AS 拆分后列名
FROM 
    原表名 t
-- CROSS APPLY会过滤掉拆分列为空的行，如果要保留空值行，换成OUTER APPLY
CROSS APPLY 
    STRING_SPLIT(t.要拆分的列名, '|') s

要是需要保留原列是空值的行，把CROSS APPLY改成OUTER APPLY就行，同时可以用ISNULL处理空字符串：

OUTER APPLY STRING_SPLIT(ISNULL(t.要拆分的列名, ''), '|') s

2. 通用可复用存储过程（适配未来未知表） #

因为你要处理至少4个未知的未来表，写一个动态SQL的存储过程是最优解——传入表名、拆分列名、输出表名，一键完成拆分，完全不需要手动写列名：

CREATE PROCEDURE dbo.SplitColumnToRows
    @SourceTableName NVARCHAR(128), -- 源表名
    @SplitColumnName NVARCHAR(128), -- 要拆分的列名
    @TargetTableName NVARCHAR(128)  -- 拆分后生成的新表名
AS
BEGIN
    SET NOCOUNT ON;

    -- 自动获取源表除拆分列外的所有列名
    DECLARE @Columns NVARCHAR(MAX);
    SELECT @Columns = STRING_AGG(QUOTENAME(c.name), ', ')
    FROM sys.columns c
    WHERE c.object_id = OBJECT_ID(@SourceTableName)
      AND c.name != @SplitColumnName;

    -- 生成动态拆分SQL
    DECLARE @SQL NVARCHAR(MAX) = N'
        SELECT 
            ' + @Columns + ',
            s.value AS ' + QUOTENAME(@SplitColumnName + '_Split') + '
        INTO ' + @TargetTableName + '
        FROM ' + @SourceTableName + ' t
        -- 用OUTER APPLY保留空值行，ISNULL处理空字符串
        OUTER APPLY STRING_SPLIT(ISNULL(t.' + QUOTENAME(@SplitColumnName) + ', ''''), ''|'') s
        -- 要是不需要空值的拆分结果，加这句：WHERE s.value != ''''
    ';

    EXEC sp_executesql @SQL;
END
GO

-- 使用示例：直接调用存储过程就行
EXEC dbo.SplitColumnToRows 
    @SourceTableName = '你的源表名',
    @SplitColumnName = '带竖线的列名',
    @TargetTableName = '拆分后的新表名';

这个存储过程会自动识别源表的所有结构，不管未来的表是什么样，只要传入参数就能用，完全适配拼接值列表的变更。

3. 旧版本SQL Server兼容写法（2016之前） #

如果你的SQL Server版本低于2016，没有STRING_SPLIT，先创建一个通用的拆分函数：

CREATE FUNCTION dbo.SplitString
(
    @String NVARCHAR(MAX),
    @Delimiter CHAR(1)
)
RETURNS @Result TABLE (Value NVARCHAR(MAX))
AS
BEGIN
    DECLARE @StartIndex INT = 1, @EndIndex INT;
    -- 确保字符串末尾有分隔符，避免遗漏最后一个值
    IF SUBSTRING(@String, LEN(@String), 1) != @Delimiter
        SET @String = @String + @Delimiter;

    WHILE CHARINDEX(@Delimiter, @String) > 0
    BEGIN
        SET @EndIndex = CHARINDEX(@Delimiter, @String);
        INSERT INTO @Result(Value)
        SELECT SUBSTRING(@String, @StartIndex, @EndIndex - @StartIndex);
        SET @String = SUBSTRING(@String, @EndIndex + 1, LEN(@String));
    END
    RETURN;
END
GO

之后把上面的STRING_SPLIT替换成dbo.SplitString就行，用法完全一致。

R 方案（灵活高效，适合批量处理） #

如果你的工作流涉及R，或者需要批量处理多个表，tidyr包的separate_rows函数简直是为这个场景量身定做的，代码简洁到离谱，还容易封装成通用函数：

1. 基础拆分写法 #

library(tidyr)

# 拆分指定列，sep要用\\|因为|是正则特殊字符
df_split <- separate_rows(你的数据框, 要拆分的列名, sep = "\\|")

默认会保留原列是空值的行，要是不需要空值结果，可以加个过滤：

df_split <- df_split %>% filter(!is.na(要拆分的列名) & 要拆分的列名 != "")

2. 通用复用函数（适配多表批量处理） #

写一个简单的函数，以后不管处理什么表，直接调用就行：

library(tidyr)
library(dplyr)

split_column_to_rows <- function(data, col_name, sep = "\\|", keep_original_col = FALSE) {
  # 参数说明：
  # data: 输入的数据框
  # col_name: 要拆分的列名（字符串）
  # sep: 分隔符，默认是竖线
  # keep_original_col: 是否保留原拆分列，默认不保留
  
  result <- data %>%
    separate_rows(all_of(col_name), sep = sep)
  
  # 按需移除原拆分列
  if (!keep_original_col) {
    result <- result %>% select(-all_of(col_name))
  }
  
  return(result)
}

# 使用示例：
# 单表拆分
df1_split <- split_column_to_rows(df1, "带竖线的列名")

# 批量处理多个表
list_of_tables <- list(df1, df2, df3, df4)
split_tables <- lapply(list_of_tables, split_column_to_rows, col_name = "带竖线的列名")

这个函数完全不限制拆分值的数量，未来拼接值列表变了也不需要改代码，适配任何未知表。

方案选择建议 #

• 如果你的工作流主要在SQL Server里，优先用T-SQL存储过程，可以直接在数据库中重复执行，不需要额外工具，适合定期自动化执行。
• 如果需要批量处理多个表、或者要和其他数据清洗步骤结合，R的方案更灵活，代码更简洁，学习成本低。
• 两种方案都不需要硬编码拆分值的数量，完美适配未来拼接值列表的变更或新增。

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