SQL Server 2008 R2 如何拆分~分隔符单列数据并写入对应多列

SQL Server 2008 R2 没有内置的字符串拆分函数，以下两种方案均可实现需求：

无需自定义函数的XML方案（适合临时使用） #

通过将分隔字符串转换为XML格式直接按索引提取对应字段，无需额外创建数据库对象，直接替换原有SQL即可：

INSERT INTO TempTable2 (Offer1, Offer2)
SELECT 
    -- 提取第1个~分隔的字段，要取第N个就修改x[N]的数字即可
    CAST('<x>' + REPLACE(OfferString, '~', '</x><x>') + '</x>' AS XML).value('/x[1]', 'varchar(8000)') AS Offer1,
    -- 提取第2个~分隔的字段
    CAST('<x>' + REPLACE(OfferString, '~', '</x><x>') + '</x>' AS XML).value('/x[2]', 'varchar(8000)') AS Offer2
FROM TempTable

实现逻辑：先把字符串内的~替换为XML标签分隔符，拼成合法的XML结构后，用value方法按索引读取对应节点的内容即可。

自定义拆分函数方案（适合频繁拆分场景） #

如果业务中经常需要做字符串拆分，可以先创建通用拆分函数：

CREATE FUNCTION dbo.SplitString
(
    @InputString VARCHAR(MAX),
    @Delimiter CHAR(1)
)
RETURNS @Result TABLE (ItemIndex INT, ItemValue VARCHAR(8000))
AS
BEGIN
    DECLARE @Index INT = 1, @ItemValue VARCHAR(8000)
    WHILE CHARINDEX(@Delimiter, @InputString) > 0
    BEGIN
        SET @ItemValue = SUBSTRING(@InputString, 1, CHARINDEX(@Delimiter, @InputString) - 1)
        INSERT INTO @Result (ItemIndex, ItemValue) VALUES (@Index, @ItemValue)
        SET @InputString = SUBSTRING(@InputString, CHARINDEX(@Delimiter, @InputString) + 1, LEN(@InputString))
        SET @Index = @Index + 1
    END
    INSERT INTO @Result (ItemIndex, ItemValue) VALUES (@Index, @InputString)
    RETURN
END

调用函数实现字段提取的SQL写法：

INSERT INTO TempTable2 (Offer1, Offer2)
SELECT
    MAX(CASE WHEN s.ItemIndex = 1 THEN s.ItemValue END) AS Offer1,
    MAX(CASE WHEN s.ItemIndex = 2 THEN s.ItemValue END) AS Offer2
FROM TempTable t
CROSS APPLY dbo.SplitString(t.OfferString, '~') s
GROUP BY t.OfferString -- 表有主键的情况下建议替换为主键分组，性能更高

注意事项 #

• 如果待拆分的字符串包含XML特殊字符（&、<、>），需要先做转义处理再拼接XML，避免解析报错
• 示例中的varchar(8000)可根据实际业务字段长度调整，Unicode内容请替换为nvarchar对应长度
• 拆分位置的字段不存在时会返回NULL，不会抛出异常

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