SQL正则表达式校验字段格式：解决可选参数缺失匹配问题

我正尝试校验某个字段的值是否符合特定格式（格式规则参考下图）：

当前用正则匹配的方案能覆盖大部分场景，但当可选参数value_5缺失时就匹配失败，想找更严谨且优雅的校验方式。

现有两段SQL：

• 这段能匹配但逻辑不严谨（用.*允许末尾任意字符）：

SELECT REGEXP_LIKE('3-Abc-Def_ValueNumber2_ValueNumber3_ValueNumber4_ValueNumber5','[0-9]{1,2}-[A-Za-z0-9]{3}-[A-Za-z0-9]{3}_[A-Za-z0-9]{3,17}_[A-Za-z0-9]{3,17}_[A-Za-z0-9]{3,20}_[A-Za-z0-9]{3,20}.*');

• 这段硬编码了所有参数位置，value_5缺失时直接匹配失败：

SELECT REGEXP_LIKE('3-Abc-Def_ValueNumber2_ValueNumber3_ValueNumber4_ValueNumber5_ValueNumber6','[0-9]{1,2}-[A-Za-z0-9]{3}-[A-Za-z0-9]{3}_[A-Za-z0-9]{3,17}_[A-Za-z0-9]{3,17}_[A-Za-z0-9]{3,20}_[A-Za-z0-9]{3,20}_[A-Za-z0-9]{3,20}.*');

测试样本值：

'3-ABC-DEF_GHI_ValueNumber2_ValueNumber3_ValueNumber4__ValueNumber6'
'23-ABC-DEF_GHI_ValueNumber2_Value3_ValueNumber4'
'23-AB-DEF_GHIJK_ValueNumber2_ValueNumber3_ValueNumber4_ValueNumber5'

优化后的正则校验方案 #

核心思路是把可选的value_5部分标记为可选匹配，同时用^和$确保字符串完全匹配，避免宽松的.*带来的无效匹配：

SELECT REGEXP_LIKE(你的字段名, '^[0-9]{1,2}-[A-Za-z0-9]{3}-[A-Za-z0-9]{3}_[A-Za-z0-9]{3,17}_[A-Za-z0-9]{3,17}_[A-Za-z0-9]{3,20}(?:_[A-Za-z0-9]{3,20})?_[A-Za-z0-9]{3,20}$');

关键调整说明 #

• ^ 和 $：强制从字符串开头到结尾完全匹配，杜绝末尾出现无关字符的情况
• (?:_[A-Za-z0-9]{3,20})?：将value_5的前缀下划线和内容包装成可选非捕获组，?表示该部分可以出现0次（即缺失）或1次（即存在）
• 保留各段长度限制：严格对应格式要求的位数范围，比如前两个字母段固定3位，后续value段分别对应3-17、3-17、3-20、3-20、3-20的长度要求

针对测试样本的验证 #

1. '3-ABC-DEF_GHI_ValueNumber2_ValueNumber3_ValueNumber4__ValueNumber6'：这里出现了__（两个下划线），相当于value_5为空值。如果你的格式允许value_5为空，需要把可选组的长度限制改成{0,20}（即(?:_[A-Za-z0-9]{0,20})?），否则这个样本会因不符合长度要求被拒绝
2. '23-ABC-DEF_GHI_ValueNumber2_Value3_ValueNumber4'：这个样本缺少value_5和value_6，如果value_6是必填项，那它本身不符合格式，正则会正确拒绝；如果value_6也可选，需要再给value_6部分也加上可选标记
3. '23-AB-DEF_GHIJK_ValueNumber2_ValueNumber3_ValueNumber4_ValueNumber5'：前两个字母段中第一个是2位（不符合格式要求的3位），正则会直接拒绝这个无效值

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