SQL中如何校验VARCHAR类型编码字段符合指定格式规则

补充校验后的完整SQL查询 #

你可以直接在原有WHERE条件中追加两段校验逻辑即可覆盖剩余规则：

SELECT CODE
FROM CODES
WHERE 
  -- 原有已实现的校验规则
  SUBSTRING(CODE, 1, 4) = 'PROD'
  AND SUBSTRING(CODE, 5, 1) = '_'
  AND SUBSTRING(CODE, 6, 2) IN ('OP','UG','TR')
  AND RIGHT(YEAR(analysis_date), 2) = SUBSTRING(CODE, 8, 2) 
  AND SUBSTRING(CODE, 10, 1) = '_'
  AND (
    LEN(CODE) = 13
    OR 
    (LEN(CODE) = 14 AND CODE LIKE '%R')
  )
  -- 新增：校验第11位起的3个字符全部为数字
  AND SUBSTRING(CODE, 11, 3) NOT LIKE '%[^0-9]%'
  -- 新增：校验编码不存在小写字母
  AND BINARY CODE = UPPER(CODE)

逻辑说明 #

1. 三位数字校验：SUBSTRING(CODE, 11, 3) NOT LIKE '%[^0-9]%' 表示截取的3个字符中不包含任何非0-9的字符，比ISNUMERIC判断更精准，兼容MySQL、SQL Server等主流数据库语法。
2. 大小写校验：BINARY CODE = UPPER(CODE) 会区分大小写对比原编码和转全大写后的编码，完全相等即说明原编码无小写字符。如果使用SQL Server，可以将BINARY替换为COLLATE Chinese_PRC_CS_AS这类区分大小写的排序规则即可生效。

简化方案（支持正则的数据库可用） #

如果你的数据库支持正则表达式，可以大幅简化整体校验逻辑：

-- MySQL 环境示例
SELECT CODE
FROM CODES
WHERE 
  CODE REGEXP '^PROD_(OP|UG|TR)[0-9]{2}_[0-9]{3}R?$'
  AND RIGHT(YEAR(analysis_date), 2) = SUBSTRING(CODE, 8, 2)
  AND BINARY CODE = UPPER(CODE)

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