SQL Server 2012中如何在SELECT语句中仅保留VARCHAR列的指定有效字符？

针对SQL Server 2012中保留指定有效字符的需求，推荐两种灵活的实现方案：

方案一：自定义标量函数（推荐，高灵活性） #

创建可复用的函数，接收输入字符串和有效字符集合，逐字符检查并保留有效字符，后续修改有效字符只需调整调用参数即可。

1. 创建清理函数 #

CREATE FUNCTION dbo.CleanInvalidChars
(
    @InputString VARCHAR(MAX),
    @ValidChars VARCHAR(100) -- 传入需要保留的有效字符集合
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @CleanString VARCHAR(MAX) = '';
    DECLARE @Position INT = 1;
    
    WHILE @Position <= LEN(@InputString)
    BEGIN
        DECLARE @CurrentChar CHAR(1) = SUBSTRING(@InputString, @Position, 1);
        -- 如需严格区分大小写，可去掉UPPER转换
        IF CHARINDEX(UPPER(@CurrentChar), UPPER(@ValidChars)) > 0
        BEGIN
            SET @CleanString = @CleanString + @CurrentChar;
        END
        SET @Position = @Position + 1;
    END
    
    RETURN @CleanString;
END
GO

2. 使用函数查询 #

调用时直接传入目标字符串和有效字符集合，示例：

DECLARE @table AS TABLE
(
    ID INT ,
    Name VARCHAR(500) ,
    Age INT 
)

INSERT INTO @table 
VALUES (1, 'Hello ## World! Test8.?##', 23), 
       (2, 'Need specific characters only Test8.? ]]', 22)

-- 指定有效字符：大小写字母、数字、问号
SELECT 
    ID,
    dbo.CleanInvalidChars(Name, 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789?') AS CleanedName,
    Age
FROM @table;

进阶：结合配置表实现动态有效字符 #

如果有效字符需要频繁变动，可将其存入配置表，查询时动态读取，无需修改业务代码：

-- 创建配置表
CREATE TABLE ValidCharsConfig
(
    ConfigID INT PRIMARY KEY,
    ValidChars VARCHAR(100) NOT NULL
);

-- 插入有效字符配置
INSERT INTO ValidCharsConfig VALUES (1, 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789?');

-- 动态读取配置查询
SELECT 
    t.ID,
    dbo.CleanInvalidChars(t.Name, vc.ValidChars) AS CleanedName,
    t.Age
FROM @table t
CROSS JOIN ValidCharsConfig vc
WHERE vc.ConfigID = 1;

方案二：递归CTE（无需创建对象，临时查询适用） #

如果不想创建函数，可使用递归CTE逐字符处理字符串，适合一次性临时查询场景：

DECLARE @table AS TABLE
(
    ID INT ,
    Name VARCHAR(500) ,
    Age INT 
)

INSERT INTO @table 
VALUES (1, 'Hello ## World! Test8.?##', 23), 
       (2, 'Need specific characters only Test8.? ]]', 22)

WITH RecursiveClean AS
(
    SELECT 
        ID,
        Name,
        Age,
        1 AS Position,
        CASE WHEN CHARINDEX(SUBSTRING(Name, 1, 1), 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789?') > 0 
             THEN SUBSTRING(Name, 1, 1) 
             ELSE '' 
        END AS CleanedName
    FROM @table
    UNION ALL
    SELECT 
        ID,
        Name,
        Age,
        Position + 1,
        CleanedName + CASE WHEN CHARINDEX(SUBSTRING(Name, Position + 1, 1), 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789?') > 0 
                           THEN SUBSTRING(Name, Position + 1, 1) 
                           ELSE '' 
                      END
    FROM RecursiveClean
    WHERE Position < LEN(Name)
)
SELECT 
    ID,
    MAX(CleanedName) AS CleanedName,
    Age
FROM RecursiveClean
GROUP BY ID, Age
OPTION (MAXRECURSION 0); -- 允许递归次数匹配字符串长度，设0表示无限制

方案说明 #

• 函数方案适合长期复用，配合配置表可实现有效字符的动态调整，无需修改代码。
• 递归CTE适合临时查询场景，无需创建数据库对象，但修改有效字符时需调整CTE内的字符集合。

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