SQL Server按可变子串拆分字符串的数据清洗方案求助

针对你在SQL Server 15.0.18384.0中遇到的按「数字+句号+空格」拆分长字符串的问题，提供两种可行方案：

方案一：递归CTE拆分（无需自定义函数） #

这种方法通过递归逐个提取每个条目，无需依赖正则或额外函数，适合内容中包含数字+句号的场景：

假设你的表名为YourTable，ColumnA是需要保留的重复列，OriginalString是存储长字符串的列，执行以下SQL：

WITH SplitCTE AS (
    -- 初始行：处理原始字符串，提取第一个条目
    SELECT
        ColumnA,
        -- 提取第一个条目内容：从第一个". "后到下一个". "前的部分
        SUBSTRING(
            TRIM('"' FROM OriginalString),
            CHARINDEX('. ', TRIM('"' FROM OriginalString)) + 2,
            CASE 
                WHEN CHARINDEX('. ', TRIM('"' FROM OriginalString), CHARINDEX('. ', TRIM('"' FROM OriginalString)) + 2) = 0 
                THEN LEN(TRIM('"' FROM OriginalString)) - CHARINDEX('. ', TRIM('"' FROM OriginalString)) - 1
                ELSE CHARINDEX('. ', TRIM('"' FROM OriginalString), CHARINDEX('. ', TRIM('"' FROM OriginalString)) + 2) - CHARINDEX('. ', TRIM('"' FROM OriginalString)) - 2
            END
        ) AS SplitValue,
        -- 生成剩余未处理的字符串
        STUFF(
            TRIM('"' FROM OriginalString),
            1,
            CASE 
                WHEN CHARINDEX('. ', TRIM('"' FROM OriginalString), CHARINDEX('. ', TRIM('"' FROM OriginalString)) + 2) = 0 
                THEN LEN(TRIM('"' FROM OriginalString))
                ELSE CHARINDEX('. ', TRIM('"' FROM OriginalString), CHARINDEX('. ', TRIM('"' FROM OriginalString)) + 2)
            END,
            ''
        ) AS RemainingString
    FROM YourTable

    UNION ALL

    -- 递归提取后续条目
    SELECT
        ColumnA,
        SUBSTRING(
            RemainingString,
            CHARINDEX('. ', RemainingString) + 2,
            CASE 
                WHEN CHARINDEX('. ', RemainingString, CHARINDEX('. ', RemainingString) + 2) = 0 
                THEN LEN(RemainingString) - CHARINDEX('. ', RemainingString) - 1
                ELSE CHARINDEX('. ', RemainingString, CHARINDEX('. ', RemainingString) + 2) - CHARINDEX('. ', RemainingString) - 2
            END
        ) AS SplitValue,
        STUFF(
            RemainingString,
            1,
            CASE 
                WHEN CHARINDEX('. ', RemainingString, CHARINDEX('. ', RemainingString) + 2) = 0 
                THEN LEN(RemainingString)
                ELSE CHARINDEX('. ', RemainingString, CHARINDEX('. ', RemainingString) + 2)
            END,
            ''
        ) AS RemainingString
    FROM SplitCTE
    WHERE LEN(RemainingString) > 0
)
SELECT ColumnA, SplitValue AS ColumnB
FROM SplitCTE
OPTION (MAXRECURSION 0); -- 若条目数超过100，需添加此选项解除递归深度限制

说明： #

1. 先通过TRIM('"' FROM OriginalString)移除字符串首尾的引号；
2. 每次递归找到. （数字后的句号+空格）的位置，提取中间的内容作为拆分结果；
3. 用STUFF截断已处理的部分，继续处理剩余字符串直到为空。

方案二：自定义函数+STRING_SPLIT（简洁高效） #

如果能确保内容中不存在.数字. 格式的子串，可先将分隔符统一替换为@，再用STRING_SPLIT拆分：

步骤1：创建自定义替换函数 #

CREATE FUNCTION [dbo].[ReplaceEntrySeparators](@Temp VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
    -- 移除首尾引号
    SET @Temp = TRIM('"' FROM @Temp)
    
    DECLARE @Pos INT
    -- 查找所有".数字. "格式的分隔符
    SET @Pos = PATINDEX('%.[0-9]. %', @Temp)
    
    WHILE @Pos > 0
    BEGIN
        -- 将".数字. "替换为@（长度为4，例如".2. "是4个字符）
        SET @Temp = STUFF(@Temp, @Pos, 4, '@')
        -- 继续查找下一个分隔符
        SET @Pos = PATINDEX('%.[0-9]. %', @Temp)
    END
    
    -- 移除开头的"数字. "部分
    SET @Temp = STUFF(@Temp, 1, CHARINDEX('. ', @Temp) + 1, '')
    
    RETURN @Temp
END

步骤2：拆分字符串 #

SELECT 
    t.ColumnA, 
    s.value AS ColumnB
FROM YourTable t
CROSS APPLY STRING_SPLIT(dbo.ReplaceEntrySeparators(t.OriginalString), '@') s

说明： #

1. 函数专门定位作为条目分隔符的.数字. ，避免误替换内容中的数字+句号；
2. 处理后的字符串格式为内容1@内容2@内容3...，直接用STRING_SPLIT拆分即可。

原自定义函数失效原因 #

你之前的函数使用%[0-9].%作为匹配模式，会匹配所有包含「数字+句号」的子串（包括内容中的），且未准确定位作为分隔符的数字. ，导致替换逻辑错误，无法达到预期效果。

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