旧版SQL Server如何将管道分隔字符串拆分后横向插入另一表多列

问题背景 #

• 源表存储|分隔的定长字符串，示例：MY|NAME|IS|ABCD|ZHGGG|GSHHS|ASDF|ASDF
• 目标表包含col1~col8共8个字段，要求拆分后第N段内容存入对应colN字段
• 环境为不支持STRING_SPLIT的旧版本SQL Server，现有自定义拆分函数仅能返回多行单列的垂直拆分结果，无法满足横向入列要求

最优实现方案 #

固定8列的场景下，不需要使用拆分函数+行转列的冗余逻辑，直接基于CHARINDEX定位分隔符位置+SUBSTRING截取对应分段即可，全版本SQL Server兼容，执行效率远高于行转列方案。

直接写入目标表的实现代码 #

INSERT INTO target_table (col1, col2, col3, col4, col5, col6, col7, col8)
SELECT
    SUBSTRING(pipe_str, 1, p1.pos - 1) AS col1,
    SUBSTRING(pipe_str, p1.pos + 1, p2.pos - p1.pos - 1) AS col2,
    SUBSTRING(pipe_str, p2.pos + 1, p3.pos - p2.pos - 1) AS col3,
    SUBSTRING(pipe_str, p3.pos + 1, p4.pos - p3.pos - 1) AS col4,
    SUBSTRING(pipe_str, p4.pos + 1, p5.pos - p4.pos - 1) AS col5,
    SUBSTRING(pipe_str, p5.pos + 1, p6.pos - p5.pos - 1) AS col6,
    SUBSTRING(pipe_str, p6.pos + 1, p7.pos - p6.pos - 1) AS col7,
    SUBSTRING(pipe_str, p7.pos + 1, LEN(pipe_str) - p7.pos) AS col8
FROM source_table
-- 预计算每个分隔符的位置，避免嵌套CHARINDEX降低可读性
CROSS APPLY (SELECT CHARINDEX('|', pipe_str, 1) AS pos) p1
CROSS APPLY (SELECT CHARINDEX('|', pipe_str, p1.pos + 1) AS pos) p2
CROSS APPLY (SELECT CHARINDEX('|', pipe_str, p2.pos + 1) AS pos) p3
CROSS APPLY (SELECT CHARINDEX('|', pipe_str, p3.pos + 1) AS pos) p4
CROSS APPLY (SELECT CHARINDEX('|', pipe_str, p4.pos + 1) AS pos) p5
CROSS APPLY (SELECT CHARINDEX('|', pipe_str, p5.pos + 1) AS pos) p6
CROSS APPLY (SELECT CHARINDEX('|', pipe_str, p6.pos + 1) AS pos) p7

异常兼容处理 #

如果源数据存在分隔段数量不足8个的情况，可以给每个截取逻辑加判断，避免分隔符不存在时CHARINDEX返回0导致的长度参数为负报错：

-- 以col1为例，其余字段逻辑一致
CASE WHEN p1.pos > 0 THEN SUBSTRING(pipe_str, 1, p1.pos -1) ELSE pipe_str END AS col1

可复用标量函数封装 #

如果需要在多个场景复用拆分逻辑，可以封装一个按序号取分段的标量函数，不需要返回多行结果集：

CREATE FUNCTION dbo.GetSplitPart(
    @Str VARCHAR(MAX),
    @Sep CHAR(1),
    @PartIndex INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @cur INT = 0, @prev INT = 0, @idx INT = 0
    WHILE @cur <= LEN(@Str)
    BEGIN
        SET @cur = CHARINDEX(@Sep, @Str, @cur + 1)
        SET @idx = @idx + 1
        IF @idx = @PartIndex
        BEGIN
            IF @cur = 0 SET @cur = LEN(@Str) + 1
            RETURN SUBSTRING(@Str, @prev + 1, @cur - @prev - 1)
        END
        SET @prev = @cur
        IF @cur = 0 BREAK
    END
    RETURN NULL
END

函数调用方式：

INSERT INTO target_table (col1,col2,col3,col4,col5,col6,col7,col8)
SELECT
    dbo.GetSplitPart(pipe_str,'|',1),
    dbo.GetSplitPart(pipe_str,'|',2),
    dbo.GetSplitPart(pipe_str,'|',3),
    dbo.GetSplitPart(pipe_str,'|',4),
    dbo.GetSplitPart(pipe_str,'|',5),
    dbo.GetSplitPart(pipe_str,'|',6),
    dbo.GetSplitPart(pipe_str,'|',7),
    dbo.GetSplitPart(pipe_str,'|',8)
FROM source_table

方案说明 #

之前使用的多行返回拆分函数，本质是把字符串拆成N行数据，要转成多列必须额外加序号列再做PIVOT行转列，逻辑冗余且性能差，固定列数的分隔字符串拆分场景下，直接定位截取是最高效、兼容性最好的实现方式。

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