如何使用SQL创建存储0-31范围ASCII控制键的表

原实现的核心问题 #

• 控制字符生成逻辑完全错误：你当前用CAST(n AS varchar(1))是把数字本身转为字符串格式，比如n=10会得到字符串'10'，被varchar(1)截断为'1'，完全不是ASCII码为10的换行控制符。正确生成对应ASCII字符应该用CHAR(n)函数。
• 字段设计冗余不合理：[character]字段存储字符后后续查询又强转为int，完全多此一举，直接存储ASCII码值和对应字符即可。
• 查询逻辑有冗余+返回重复数据风险：用CROSS JOIN关联32行控制表，若单条路径包含多个控制字符，同一条记录会多次返回；且大表场景下关联过滤效率低于直接匹配。

更合理的实现方案 #

方案1：保留控制字符表（适合需要多次关联查询的场景） #

首先修正建表和插入逻辑：

CREATE TABLE control_characters(
    ascii_code INT PRIMARY KEY, -- 直接存储ASCII码值，无需后续强转
    control_char CHAR(1) NOT NULL -- 存储对应控制字符
);

-- 递归生成0-31的控制字符
WITH cte AS (
    SELECT 0 AS n
    UNION ALL
    SELECT n+1 FROM cte WHERE n <= 30
)
INSERT INTO control_characters(ascii_code, control_char)
SELECT n, CHAR(n) FROM cte;

然后修正查询逻辑，可按需选择去重或返回所有匹配项：

-- 场景A：返回所有匹配的控制码+对应记录（允许同条记录多次返回，对应不同控制字符）
SELECT cc.ascii_code AS pb, s.*
FROM file_extract_staging s
INNER JOIN control_characters cc 
    ON CHARINDEX(cc.control_char, s.absolute_path_unique) > 0

-- 场景B：仅返回包含控制字符的去重记录，不需要返回具体控制码，用EXISTS效率更高
SELECT s.*
FROM file_extract_staging s
WHERE EXISTS (
    SELECT 1 FROM control_characters cc 
    WHERE CHARINDEX(cc.control_char, s.absolute_path_unique) > 0
)

方案2：无需建控制字符表，直接匹配（适合单次查询场景，效率更高） #

直接用LIKE通配符匹配ASCII 0-31范围的字符，一行代码即可完成筛选：

SELECT *
FROM file_extract_staging
WHERE absolute_path_unique LIKE '%[' + CHAR(0) + '-' + CHAR(31) + ']%'

如果需要同时返回匹配到的控制码，可以结合STRING_SPLIT或者其他函数实现，比建物理表更轻量。

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