如何使用递归CTE去除SQL表字段中的特殊字符？

当然可以用递归CTE来实现这个需求！递归CTE特别适合这种需要逐步迭代处理字符串、逐个移除特殊字符的场景。下面我会给你具体的实现方案和代码示例。

实现思路 #

递归CTE分为两个核心部分：

1. 锚点成员：负责初始化数据，把原始的脏数据加载进来。
2. 递归成员：每次从当前字符串中找到第一个特殊字符并移除，重复这个过程直到字符串里没有特殊字符为止。

我们这里定义「特殊字符」为非字母、非数字的字符，如果你的需求有调整（比如要保留某些符号），只需要修改匹配规则即可。

具体代码示例 #

首先先创建测试表并插入你的示例数据：

CREATE TABLE TestTable (CardCorporateName VARCHAR(100));
INSERT INTO TestTable 
VALUES ('~$%/*dfgdggf/*('), ('^^&@~`58964sdhfdk-+*/-=-0');

然后用递归CTE完成清理：

WITH RecursiveCleanup AS (
    -- 锚点成员：加载原始数据，初始化清理后的字符串为原始值
    SELECT 
        CardCorporateName AS OriginalString,
        CardCorporateName AS CleanedString
    FROM TestTable

    UNION ALL

    -- 递归成员：每次移除第一个非字母数字的字符
    SELECT 
        OriginalString,
        -- 用STUFF替换掉找到的第一个特殊字符
        STUFF(
            CleanedString, 
            PATINDEX('%[^a-zA-Z0-9]%', CleanedString), 
            1, 
            ''
        ) AS CleanedString
    FROM RecursiveCleanup
    -- 终止条件：当字符串中没有特殊字符时停止递归
    WHERE PATINDEX('%[^a-zA-Z0-9]%', CleanedString) > 0
)
-- 提取最终的清理结果（过滤掉中间迭代步骤）
SELECT 
    CleanedString AS CardCorporateName
FROM RecursiveCleanup
WHERE PATINDEX('%[^a-zA-Z0-9]%', CleanedString) = 0
ORDER BY OriginalString;

代码说明 #

• PATINDEX('%[^a-zA-Z0-9]%', CleanedString)：用来定位字符串中第一个非字母数字的字符位置，[^a-zA-Z0-9]是正则匹配规则，表示「不是字母也不是数字」。
• STUFF函数：用来把找到的特殊字符替换为空字符串，实现移除操作。
• 递归会持续执行，直到CleanedString中找不到任何特殊字符，最后我们只取那些完全清理好的行即可。

执行结果 #

运行上面的代码后，你会得到期望的输出：

如果需要调整特殊字符的定义，比如要保留下划线_，只需要把PATINDEX里的规则改成%[^a-zA-Z0-9_]%就行。

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