Redshift中表列值合规校验的模式匹配方法选型与实现

• SIMILAR TO：SQL标准内置的布尔型匹配函数，默认对输入字符串做全量匹配，语法简化兼容LIKE规则，适合固定校验规则下的强合规判断，不需要额外处理匹配边界问题。
• REGEXP_COUNT：核心功能是统计字符串内正则匹配片段的出现次数，本身不属于纯校验类函数，仅当明确需要统计匹配频次时才推荐使用；如果用它做合规校验，必须在正则表达式首尾加上^和$边界符，锁定匹配范围为整个字符串，否则会出现“片段匹配即判定合规”的严重漏判。

原测试语句SELECT REGEXP_COUNT('6A', '^[0-9]+(R|MR|MS|S)?')存在四处逻辑漏洞：

1. 缺失字符串结尾边界$：正则仅锚定字符串开头，只要开头是数字，无论后续拼接什么内容都会被识别为匹配片段，比如'6A'中开头的数字'6'完全符合片段匹配规则，此时函数返回1，无法识别出该值不合规。
2. 未限制数字取值范围：当前正则支持任意长度的数字组合，999R、1000MS这类超出0-100范围的值也会被判定为合规。
3. 后缀逻辑与规则冲突：正则中后缀部分用?标记为可选，即匹配0次也能通过，会出现纯数字无后缀的值也能通过校验的问题，不符合“必须拼接合法后缀”的规则要求。
4. 未拦截夹杂异常字符的情况：如12R34、20MR5这类数字与后缀交错拼接的异常值，没有对应的拦截规则。

先把校验规则拆解为可识别的正则逻辑：数字部分严格覆盖0-100范围（含0、单个1-9数字、两位10-99数字、100），数字后必须紧跟R/MR/MS/S四类合法后缀之一，整个字符串无额外多余字符。

方案1：SIMILAR TO实现（优先推荐） #

该方案逻辑简洁，无边界匹配漏判风险，代码如下：

-- 逐行校验表中目标列的合规性：返回1为合规，0为不合规
SELECT
  target_col,
  CASE
    WHEN target_col SIMILAR TO '(0|[1-9][0-9]?|100)(R|MR|MS|S)' THEN 1
    ELSE 0
  END AS valid_flag
FROM target_table;

正则段逻辑说明：

• 数字段(0|[1-9][0-9]?|100)：覆盖0-100的所有合法值，自动拦截带前导零的非法值（如01、005）、超出范围的非法值（如101、999）
• 后缀段(R|MR|MS|S)：强制要求必须匹配四类合法后缀，无可选匹配逻辑
• 依托SIMILAR TO的全量匹配特性，自动拦截字符串前后有多余字符的异常值。

方案2：REGEXP_COUNT实现 #

如果当前数据库不支持SIMILAR TO函数，使用REGEXP_COUNT时必须补全首尾边界符，确保匹配范围为整个字符串：

SELECT
  target_col,
  CASE
    WHEN REGEXP_COUNT(target_col, '^(0|[1-9][0-9]?|100)(R|MR|MS|S)$') = 1 THEN 1
    ELSE 0
  END AS valid_flag
FROM target_table;

测试验证：使用原测试值'6A'时，因为字符串结尾为非法字符A，无法匹配结尾边界要求的后缀，REGEXP_COUNT返回0，可正确判定为不合规。

• 可通过的合规值：0R、12MS、99MR、100S
• 会被拦截的不合规值：6A、101R、20（无后缀）、01S（前导零）、20X（非法后缀）、12R34（字符交错）

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