SQL Server 2012中拆分逗号分隔字符串批量插入记录方法

嘿，在SQL Server 2012里咱们没法直接用2016才推出的STRING_SPLIT函数，但没关系，有好几种实用的方法能搞定这个拆分插入的需求，我给你详细讲讲：

方案1：利用XML拆分（无需自定义函数） #

这是最常用的临时解决方案，不需要额外创建函数，直接就能用：

DECLARE @names VARCHAR(MAX) = 'name1,name2,name3,name4';
-- 把逗号分隔的字符串转换成XML格式
DECLARE @xml XML = N'<root><r>' + REPLACE(@names, ',', '</r><r>') + '</r></root>';

-- 如果要插入到目标表，把下面的SELECT换成 INSERT INTO 你的表(目标列名)
SELECT 
    LTRIM(RTRIM(t.c.value('.', 'VARCHAR(100)'))) AS Name
FROM @xml.nodes('//root/r') t(c);

原理说明：

1. 先把原字符串里的逗号替换成XML的节点闭合/开启标签，生成一个包含多个<r>节点的XML文档
2. 用nodes()方法把每个<r>节点拆分成单独的行
3. 用value()提取节点里的文本内容，再通过LTRIM/RTRIM处理可能存在的前后空格

方案2：用递归CTE拆分 #

如果你对XML不太熟悉，递归CTE也是个不错的选择：

DECLARE @names VARCHAR(MAX) = 'name1,name2,name3,name4';

WITH SplitCTE AS (
    -- 初始步骤：提取第一个逗号前的内容，同时剩下未处理的字符串
    SELECT 
        LEFT(@names, CHARINDEX(',', @names + ',') - 1) AS Name,
        STUFF(@names, 1, CHARINDEX(',', @names + ','), '') AS RemainingString
    WHERE @names IS NOT NULL AND @names != ''
    
    UNION ALL
    
    -- 递归处理剩下的字符串，直到没有内容可拆分
    SELECT 
        LEFT(RemainingString, CHARINDEX(',', RemainingString + ',') - 1) AS Name,
        STUFF(RemainingString, 1, CHARINDEX(',', RemainingString + ','), '') AS RemainingString
    FROM SplitCTE
    WHERE RemainingString IS NOT NULL AND RemainingString != ''
)
-- 同样，替换成INSERT INTO语句即可插入到表中
SELECT LTRIM(RTRIM(Name)) AS Name
FROM SplitCTE;

原理说明：

• CTE先拆分出第一个元素，然后递归调用自己处理剩余的字符串
• 加@names + ','是为了兼容最后一个元素后面没有逗号的情况，避免遗漏

方案3：创建自定义拆分函数（可复用） #

如果你的业务里经常需要拆分字符串，不如创建一个可复用的自定义函数：

CREATE FUNCTION dbo.SplitString
(
    @inputString VARCHAR(MAX),  -- 要拆分的原字符串
    @delimiter CHAR(1)          -- 分隔符
)
RETURNS @outputTable TABLE (Item VARCHAR(100))
AS
BEGIN
    DECLARE @startIndex INT = 1;
    DECLARE @endIndex INT;
    
    -- 循环拆分每个分隔符之间的内容
    WHILE CHARINDEX(@delimiter, @inputString, @startIndex) > 0
    BEGIN
        SET @endIndex = CHARINDEX(@delimiter, @inputString, @startIndex);
        INSERT INTO @outputTable(Item)
        VALUES(LTRIM(RTRIM(SUBSTRING(@inputString, @startIndex, @endIndex - @startIndex))));
        SET @startIndex = @endIndex + 1;
    END
    
    -- 插入最后一个没有分隔符的元素
    INSERT INTO @outputTable(Item)
    VALUES(LTRIM(RTRIM(SUBSTRING(@inputString, @startIndex, LEN(@inputString) - @startIndex + 1))));
    
    RETURN;
END;

创建好函数后，以后拆分就简单了：

DECLARE @names VARCHAR(MAX) = 'name1,name2,name3,name4';

-- 插入表的话用：INSERT INTO 你的表(列名) SELECT Item FROM dbo.SplitString(@names, ',')
SELECT Item AS Name FROM dbo.SplitString(@names, ',');

一些额外提醒 #

• 建议把@names定义成VARCHAR(MAX)，避免因为字符串过长导致截断
• 如果你的字符串里包含XML特殊字符（比如&、<、>），XML方案可能会报错，这时候优先选CTE或自定义函数
• 如果需要去重，可以在SELECT语句后面加上DISTINCT关键字

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