如何在SQL列中移除两个反斜杠间的所有子串并更新列

需求说明 #

现有表T（以下用临时表#t模拟），包含Code和S列，原始数据如下：

需要移除S列每行中所有位于\和\之间的子串（包含前后的\），处理后结果如下：

现有问题 #

当前代码仅能识别并提取第一个\...\子串，无法处理同一行中多个匹配实例，也未实现对原列的更新操作。

解决方案 #

1. 处理所有...\实例 #

使用递归CTE循环处理每行字符串，直到移除所有\...\格式的子串：

IF OBJECT_ID(N'tempdb..#t') IS NOT NULL
BEGIN
    DROP TABLE #t;
END;

CREATE TABLE #t
(
    Code VARCHAR(MAX),
    S VARCHAR(MAX)
);

INSERT INTO #t (Code, S)
VALUES 
    ('1', '\String\ String'),
    ('2', 'Laser \Laser\'),
    ('3', 'La\ser\s and \S\trings');

-- 递归CTE处理所有\...\子串
WITH RecursiveClean AS (
    SELECT 
        Code,
        S AS OriginalS,
        CASE 
            WHEN CHARINDEX('\', S) > 0 AND CHARINDEX('\', S, CHARINDEX('\', S)+1) > 0
            THEN STUFF(S, CHARINDEX('\', S), CHARINDEX('\', S, CHARINDEX('\', S)+1) - CHARINDEX('\', S) + 1, '')
            ELSE S
        END AS CleanedS,
        1 AS Iteration
    FROM #t
    UNION ALL
    SELECT 
        Code,
        OriginalS,
        CASE 
            WHEN CHARINDEX('\', CleanedS) > 0 AND CHARINDEX('\', CleanedS, CHARINDEX('\', CleanedS)+1) > 0
            THEN STUFF(CleanedS, CHARINDEX('\', CleanedS), CHARINDEX('\', CleanedS, CHARINDEX('\', CleanedS)+1) - CHARINDEX('\', CleanedS) + 1, '')
            ELSE CleanedS
        END AS CleanedS,
        Iteration + 1
    FROM RecursiveClean
    WHERE CHARINDEX('\', CleanedS) > 0 AND CHARINDEX('\', CleanedS, CHARINDEX('\', CleanedS)+1) > 0
)
-- 取每行最后一次迭代的结果（即无\...\的最终字符串）
SELECT 
    Code,
    CleanedS AS FinalS
FROM (
    SELECT 
        Code,
        CleanedS,
        ROW_NUMBER() OVER (PARTITION BY Code ORDER BY Iteration DESC) AS rn
    FROM RecursiveClean
) t
WHERE rn = 1;

2. 应用UPDATE操作更新原表 #

将递归CTE的结果关联回原表，执行UPDATE完成列的修改：

-- 继续使用上面的临时表#t
WITH RecursiveClean AS (
    SELECT 
        Code,
        S AS OriginalS,
        CASE 
            WHEN CHARINDEX('\', S) > 0 AND CHARINDEX('\', S, CHARINDEX('\', S)+1) > 0
            THEN STUFF(S, CHARINDEX('\', S), CHARINDEX('\', S, CHARINDEX('\', S)+1) - CHARINDEX('\', S) + 1, '')
            ELSE S
        END AS CleanedS,
        1 AS Iteration
    FROM #t
    UNION ALL
    SELECT 
        Code,
        OriginalS,
        CASE 
            WHEN CHARINDEX('\', CleanedS) > 0 AND CHARINDEX('\', CleanedS, CHARINDEX('\', CleanedS)+1) > 0
            THEN STUFF(CleanedS, CHARINDEX('\', CleanedS), CHARINDEX('\', CleanedS, CHARINDEX('\', CleanedS)+1) - CHARINDEX('\', CleanedS) + 1, '')
            ELSE CleanedS
        END AS CleanedS,
        Iteration + 1
    FROM RecursiveClean
    WHERE CHARINDEX('\', CleanedS) > 0 AND CHARINDEX('\', CleanedS, CHARINDEX('\', CleanedS)+1) > 0
),
FinalCleaned AS (
    SELECT 
        Code,
        CleanedS
    FROM (
        SELECT 
            Code,
            CleanedS,
            ROW_NUMBER() OVER (PARTITION BY Code ORDER BY Iteration DESC) AS rn
        FROM RecursiveClean
    ) t
    WHERE rn = 1
)
UPDATE #t
SET S = fc.CleanedS
FROM #t t
JOIN FinalCleaned fc ON t.Code = fc.Code;

-- 验证更新结果
SELECT * FROM #t;

代码说明 #

• 递归CTE核心是用STUFF函数每次替换第一个出现的\...\子串，直到字符串中不存在成对的\为止。
• 通过ROW_NUMBER()窗口函数取每行最后一次迭代的结果，确保得到完全清理后的字符串。
• 最后通过JOIN关联原表和清理后的结果，执行UPDATE完成列的修改。

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