SQL 正则表达式在 SQL 中应用

mysql 8.0+ 支持 pcre 风格正则但需注意：不支持 /(?i)abc/ 等修饰符写法，大小写用 collate 控制； 仅 8.0.22+ 支持；捕获组可用于过滤但不可直接引用；regexp 与 rlike 等价，否定用 not regexp。

MySQL 8.0+ 的 REGEXP 和 RLIKE 怎么写才不报错

MySQL 在 8.0 之前只支持基本正则（POSIX ERE），8.0 起才用 ICU 库支持 PCRE 风格子组、懒匹配等特性——但默认仍禁用部分高级语法。常见错误是直接照搬 JavaScript 或 Python 正则，比如写 /(?i)abc/ 或 word，结果报错 Got Error 'repetition-operator operand invalid' from regexp。

实操建议：

• REGEXP 和 RLIKE 完全等价，选哪个纯看团队习惯；NOT REGEXP 才是否定形式，别写成 ! REGEXP
• MySQL 不支持修饰符写在正则字面量里（如 /(?i)abc/），必须靠函数或上下文控制：大小写敏感用 COLLATE utf8mb4_0900_as_cs，否则默认不区分大小写
• 单词边界  在 MySQL 8.0.22+ 才支持，低版本得用 [[:<:>:]]</:>（已废弃）或绕开：比如 CONCAT(' ', col, ' ') REGEXP ' abc '
• 捕获组可用，但不能在 select 中直接引用（不像 postgresql 的 REGEXP_MATCHES），只能用于条件过滤或配合 REGEXP_SUBSTR

PostgreSQL 的 ~、~* 和 regexp_replace() 参数陷阱

PostgreSQL 正则最常踩的坑不是语法，而是参数顺序和空值处理：regexp_replace(text, pattern, replacement) 的第四个参数 flags 是可选字符串，但一旦传了就必须是 'g'（全局）、'i'（忽略大小写）等字母组合，不能带空格或逗号，写成 'gi' 对，'g,i' 就直接报错 syntax error at or near ","。

实操建议：

• ~ 区分大小写，~* 不区分，别混淆成 LIKE 的语义；!~ 和 !~* 是否定形式
• regexp_replace() 默认只替换第一个匹配，要全局替换必须显式加 'g' 标志，漏掉就只修第一处
• 替换字符串里不能直接写
引用捕获组，得用 1（两个反斜杠），因为 SQL 字符串先解析一次转义
• 如果被匹配字段可能为 NULL，整个表达式会返回 NULL，需要提前用 COALESCE(col, '') 处理

SQL Server 没有原生正则？用 LIKE 和 STRING_SPLIT 替代的边界在哪

SQL Server 直到 2022 版才通过 TRANSLATE + STRING_AGG 组合勉强模拟简单正则逻辑，但真要匹配邮箱、手机号或提取数字片段，LIKE 的 % 和 _ 远不够用。这时候硬上 CLR 或外部脚本反而更慢，不如把清洗逻辑前置到应用层。

实操建议：

• LIKE '[0-9][0-9][0-9]' 只能匹配固定长度数字，无法处理 12345 这种变长场景；CHARINDEX + SUBSTRING 拼凑效率低且难维护
• SQL Server 2016+ 的 STRING_SPLIT 可拆分字符串，但返回无序结果，需配合 ROW_NUMBER() 才能定位第 N 个字段，不适合复杂模式提取
• 若必须在数据库内做，优先考虑 TRY_CAST 或 ISNUMERIC（注意后者对 '1e4' 也返回 1）做粗筛，再导出到应用层精筛
• SQL Server 2022 的 REGEX_* 函数仍在预览阶段，生产环境别碰

oracle 的 REGEXP_LIKE 为什么总多匹配一行

Oracle 默认按行匹配，但它的“行”定义依赖于输入数据里的换行符类型。如果字段值含 CHR(10)（LF）而客户端用的是 windows 换行（CRLF），^ 和 $ 可能失效，导致 REGEXP_LIKE(col, '^abc$') 匹配到 'abc ' 这种带回车的值。

实操建议：

• 锚点 ^/$ 默认只匹配字符串首尾，不是每行首尾；要开启多行模式，必须加标志 'm'： REGEXP_LIKE(col, '^abc$', 'm')
• Oracle 正则引擎对 Unicode 支持有限，d 只匹配 ASCII 数字，中文数字或全角数字得用 [0-9uFF10-uFF19]
• REGEXP_SUBSTR 第三个参数是起始位置（从 1 开始），第四个是出现次数，容易和 PostgreSQL 的索引习惯搞混；第四个参数为 0 表示全部匹配，不是“第零次”
• 性能敏感场景下，先用 INSTR 快速排除明显不匹配的行，再对小结果集用 REGEXP_LIKE，避免全表扫正则

事情说清了就结束