1. 先看语法和定位REGEXP_LIKE 不是普通函数而是条件表达式前几天帮人排一条校验 SQLWHERE里写了 Oracle 的REGEXP_LIKE正则表达式看起来没问题结果就是不对。这个场景在 Oracle 数据库里太常见了用RegExp_Like做手机号、身份证、邮箱、编码格式校验写起来很爽踩坑也很集中。它不是普通函数而是一个条件表达式所以放的位置、返回值、和LIKE的取舍都跟普通字符串函数不一样。适合已经会写基础 SQL、准备把校验逻辑从应用层往数据库层挪一挪的人看也适合刚接触 Oracle 正则表达式、想快速搞明白常用写法的读者。1.1 语法骨架与返回值REGEXP_LIKE的基础语法只有三个参数REGEXP_LIKE(source_string, pattern [, match_parameter])source_string是要检查的源字符串可以是表里的列、绑定变量、表达式甚至是拼接出来的字符串。pattern是正则表达式模式。match_parameter是可选匹配参数用来控制大小写、换行、多行、空白忽略等行为。它的返回值是布尔类型在 SQL 里通常只出现在WHERE、HAVING或CASE WHEN的条件位置在 PL/SQL 里可以直接放进IF判断。这里有个新手特别容易犯的错在 SQL 查询列表里直接SELECT REGEXP_LIKE(abc, a) FROM dual。这在 Oracle SQL 中是不行的因为REGEXP_LIKE是条件不是标量函数。想要看结果得包一层CASE WHENSELECT CASE WHEN REGEXP_LIKE(13800138000, ^1[3-9][0-9]{9}$) THEN 合法 ELSE 非法 END AS is_mobile FROM dual;还有一个细节是NULL。如果source_string是NULLREGEXP_LIKE的结果也是NULL而WHERE只保留结果为真的行所以空值不会被查出来。真实业务里如果字段允许为空最好用NVL兜一下或者单独把空值排除逻辑写清楚否则你会得到一种“明明有数据却查不到”的错觉。1.2 哪些场景该用 REGEXP_LIKE哪些别硬上LIKE和REGEXP_LIKE最核心的区别是LIKE只有两个通配符%表示任意多个字符_表示任意一个字符REGEXP_LIKE背后是正则引擎能表达字符集合、次数、分组、或关系、锚点这些复杂规则。简单前缀、后缀、包含查询我优先用LIKE因为写法简单执行计划也更容易友好。只有碰到“必须是 11 位手机号”“身份证格式必须符合某段规则”“邮箱必须包含 和域名后缀”“金额最多两位小数”这类强格式校验才轮到正则上场。举个实际对比例子。判断字段以A开头LIKE A%就够了没必要写REGEXP_LIKE(col, ^A)。判断字段是不是由 13 位纯数字组成LIKE写不出来这时候用REGEXP_LIKE(col, ^[0-9]{13}$)才顺理成章。再比如判断逗号分隔的数字编号列表LIKE只能粗糙地看有没有逗号正则可以写成^[0-9](,[0-9])*$把空项、多余逗号、非数字字符一起挡住。对比项LIKEREGEXP_LIKE通配能力%、_完整正则元字符、量词、分组、字符类写法复杂度低中到高执行成本通常较低通常较高复杂模式可能明显耗 CPU索引友好度前缀匹配可能走索引普通 B-tree 索引一般不能直接走适合场景简单模糊查询格式校验、复杂模式判断所以我的原则很直接能用LIKE或SUBSTR解决的问题不为了炫技写正则必须用正则时也尽量先用LIKE做粗筛再用REGEXP_LIKE做精筛。数据量小的时候感觉不出来数据量一大正则写不好就是慢查询制造机。1.3 match_parameter 五个开关i、c、n、m、xmatch_parameter是REGEXP_LIKE的第三个参数可以组合使用。很多人只记得i其实另外几个在排查问题时很关键。参数含义常用场景i不区分大小写邮箱、编码、英文名称校验c区分大小写默认行为需要严格区分大小写的编码n让点号.可以匹配换行符多行文本中包含判断m多行模式^和$匹配每行开头结尾文本按行处理x忽略模式中的空白字符把长正则拆成多行写清楚实测一个小例子SELECT match AS result FROM dual WHERE REGEXP_LIKE(Oracle, oracle, i);如果去掉i这条查询就查不出结果因为默认区分大小写。再比如x参数适合把复杂正则写成可读性更好的形式SELECT match AS result FROM dual WHERE REGEXP_LIKE( 13800138000, ^ 1 [3-9] [0-9]{9} $, x );x会忽略模式里的空白所以上面这种写法在逻辑上等价于^1[3-9][0-9]{9}$。我平时写特别长的身份证、邮箱正则时喜欢用x过两个月回头看也能看懂。但要注意字符类里面的空白是否被忽略不同写法下需要验证最稳妥的方式还是在dual里跑一遍正例和反例再上生产。2. Oracle 正则元字符与字符类写模式前先把这些吃透2.1 锚点、量词、分组与转义Oracle 正则表达式里最常用的元字符其实不多但每一个都容易写错。^表示字符串开头$表示字符串结尾。如果不加锚点REGEXP_LIKE(abc123, [0-9])也会返回真因为它只要求“包含一个数字”而不是“整个字符串都是数字”。这就是很多人校验失败的第一原因模式没有首尾锚定。常用元字符整理如下元字符作用示例^匹配开头^A表示以 A 开头$匹配结尾Z$表示以 Z 结尾.匹配任意一个字符默认不含换行a.c可匹配 abc*前一个字符出现 0 次或多次ab*匹配 a、ab、abb前一个字符出现 1 次或多次ab匹配 ab、abb?前一个字符出现 0 次或 1 次colou?r匹配 color、colour{m,n}前一个字符出现 m 到 n 次[0-9]{4,6}[]字符集合[abc]匹配 a、b、c 之一[^]取反字符集合[^0-9]匹配非数字或关系()分组(ab)匹配 ab、abab转义也要单独说。Oracle 字符串里单引号需要写成两个单引号模式里的反斜杠在 SQL 字符串中通常不用像 MySQL 那样双写。但不同客户端、不同脚本工具可能对反斜杠有额外处理。我的习惯是复杂模式用q[...]引用SELECT match AS result FROM dual WHERE REGEXP_LIKE(a.b, q[a\.b]);q[...]的好处是里面的单引号不用双写反斜杠也不容易和客户端转义打架。比如模式里要匹配OBrien这类文本用普通字符串要写OBrien用q[OBrien]就清楚很多。2.2 POSIX 字符类与 Perl 风格简写怎么选Oracle 正则基于 POSIX 风格同时支持一部分 Perl 风格简写。常用的 POSIX 字符类包括字符类含义[[:digit:]]数字[[:alpha:]]字母[[:alnum:]]字母和数字[[:upper:]]大写字母[[:lower:]]小写字母[[:space:]]空白字符[[:punct:]]标点符号[[:xdigit:]]十六进制字符Perl 风格简写里\d表示数字\D表示非数字\w表示单词字符\W表示非单词字符\s表示空白\S表示非空白。这些简写写起来短但我更推荐在正式校验里用 POSIX 字符类比如[0-9]或[[:digit:]]。原因不复杂字符集、NLS 参数、Oracle 版本存在差异时\d这种简写的边界不如显式字符类直观。尤其是迁移环境、导出脚本、跨库比对时显式写[0-9]最不容易出意外。举个例子判断一个字段是不是纯数字SELECT match AS result FROM dual WHERE REGEXP_LIKE(123456, ^[0-9]$);如果要判断字母数字混合可以写SELECT match AS result FROM dual WHERE REGEXP_LIKE(ABC123, ^[[:alnum:]]$);字符类里的^表示取反但它必须写在方括号里面的第一个位置比如[^0-9]。如果写成[0-9^]那就是数字或^字符意思完全变了。这个细节非常小但排查时很折磨人。2.3 贪婪匹配、非贪婪和回溯别让.*成为慢查询元凶Oracle 正则默认是贪婪匹配。.*会尽可能多地匹配字符遇到后续模式失败时再回溯。短字符串无所谓长文本、大表、复杂模式组合在一起回溯就会让 CPU 飙升。比如^.*[0-9].*$这种模式看起来能判断“包含数字”实际上绕了一大圈完全可以用[0-9]或^.*[0-9].*$的简化版。更好的是先明确业务到底要全串匹配还是包含匹配。需要特别提醒的是Oracle 的 POSIX 正则多数版本不支持 PCRE 里的非贪婪写法.*?也不支持前瞻、后顾、非捕获分组这类高级语法。你如果从 Python、JavaScript、PCRE 文档里直接抄一个(?...)、(?!...)、(?:...)在 Oracle 里很可能直接报ORA-12728: invalid regular expression。这不是你 SQL 写错了而是 Oracle 正则引擎不认这些语法。真需要复杂断言逻辑通常得拆成多个条件或者放到应用层、PL/SQL 里用多步判断解决。我踩过的一个坑是为了匹配“不包含某段字符”的字符串直接写了一个带负向断言的正则在测试工具里跑得好好的一放进 Oracle 就报错。后来改成先匹配包含模式再用NOT REGEXP_LIKE反向排除逻辑一样兼容性还更好。正则不是越长越厉害能让维护的人一眼看懂才是生产环境里的好正则。3. 高频业务校验实操手机号、身份证、邮箱、金额3.1 手机号与纯数字11位和13位数字怎么写手机号校验是热词里出现频率最高的场景之一。中国大陆手机号常见规则是 11 位第一位是 1第二位是 3 到 9后面 9 位数字。模式可以写成^1[3-9][0-9]{9}$实测SELECT 合法 AS result FROM dual WHERE REGEXP_LIKE(13800138000, ^1[3-9][0-9]{9}$);如果要匹配“13 位纯数字”比如某些内部编号、订单号、设备号模式就是^[0-9]{13}$或者^\d{13}$虽然手机号通常是 11 位但热词里“13位数字手机号码正则表达式怎么写”这个问法本质上是两个问题手机号规则是手机号规则13 位数字是长度规则。不要把两者混在一起。手机号有号段限制纯数字编号通常只需要长度和字符范围。如果你要校验的是“带国际区号的号码”那又是另一套规则最好先明确数据来源和格式再决定是否允许、空格、连字符。这里还有一个常见错误很多人写^1[3-9]\d{9}$之后发现有些号码查不出来。先检查源字段里有没有空格、换行、不可见字符再检查是不是用了全角数字。用TRIM处理前后空格SELECT 合法 AS result FROM dual WHERE REGEXP_LIKE(TRIM( 13800138000 ), ^1[3-9][0-9]{9}$);但如果字符串中间有空格TRIM是没用的得先用REPLACE去掉或者单独排查。校验 SQL 最怕的就是源数据不干净正则写得再对也没用。3.2 身份证号格式校验与科学计数法导出问题18 位身份证号的格式校验也是高频需求。只做格式校验不校验校验位的话可以用下面这个模式^[1-9][0-9]{5}(18|19|20)[0-9]{2}(0[1-9]|1[0-2])(0[1-9]|[12][0-9]|3[01])[0-9]{3}[0-9Xx]$这个模式拆开看就是前 6 位地区码不能以 0 开头中间 8 位是出生日期年份限定 18、19、20 开头月份 01 到 12日期 01 到 31 的粗略范围后 4 位里最后一位可以是数字或 X。注意它只做格式判断真实身份证校验位算法要复杂得多需要在应用层或 PL/SQL 里单独实现。热词里提到“oracle 数据库 SQL 导出的身份证信息是科学计数法怎么正确显示身份信息”这个坑我在实际项目里见过很多次。根本原因通常是身份证字段被建成了NUMBER类型导出到 Excel、CSV 或某些客户端时长数字被当成数值显示成科学计数法。更严重的是如果字段是NUMBER而数据里又包含 X那根本存不进去所以身份证、银行卡号、手机号这类字段表结构层面就应该用VARCHAR2。如果历史表里已经是NUMBER导出时至少要先转成字符串SELECT TO_CHAR(id_card, FM000000000000000000) AS id_card_text FROM person_info;FM去掉前导空格0格式保证补零。但如果原始数据已经因为数值精度或 X 丢失了那就不是显示问题而是数据损坏问题只能从源头修复。导出成 CSV 时还可以在值后面拼一个制表符或者要求导出工具把该列当文本处理SELECT id_card || CHR(9) AS id_card_text FROM person_info;用CHR(9)拼制表符是一种土办法但很多导出场景确实管用。更正规的方案是导出时选择“文本格式”或者直接生成 Excel 可识别的文本字段。别等到运营拿着科学计数法的表格来找你才发现字段类型一开始就选错了。3.3 邮箱、日期、金额的校验模式邮箱校验用正则很常见但要注意邮箱规则本身非常复杂严格符合标准的邮箱格式可能长到没人愿意维护。业务系统里通常用中等严格模式就够了^[A-Za-z0-9._%-][A-Za-z0-9.-]\.[A-Za-z]{2,}$实测SELECT 合法 AS result FROM dual WHERE REGEXP_LIKE(test.userexample.com, ^[A-Za-z0-9._%-][A-Za-z0-9.-]\.[A-Za-z]{2,}$);如果希望域名部分不区分大小写可以加iSELECT 合法 AS result FROM dual WHERE REGEXP_LIKE(Test.UserExample.COM, ^[A-Za-z0-9._%-][A-Za-z0-9.-]\.[A-Za-z]{2,}$, i);日期格式校验也很实用。比如要求YYYY-MM-DD^[0-9]{4}-[0-9]{2}-[0-9]{2}$这个只能校验形状不能校验 2 月 30 日这种无效日期。更严格的日期合法性可以结合TO_DATE和异常捕获处理或者用正则先粗筛再让数据库日期函数判断。金额校验可以写成^[0-9](\.[0-9]{1,2})?$它允许整数或者最多两位小数不允许负数、千分位、货币符号。如果业务允许负数就改成^-?[0-9](\.[0-9]{1,2})?$逗号分隔的数字列表也可以顺手判断^[0-9](,[0-9])*$这个模式能挡住空项、连续逗号、首尾逗号。像oracle按逗号拆分列为多行这类需求通常先用REGEXP_LIKE判断格式再用REGEXP_SUBSTR配合层级查询拆行。校验和拆分最好分开做不要在一个表达式里塞太多逻辑。3.4 在 WHERE、CASE WHEN、PL/SQL 里的不同写法同一个正则在不同位置写法不同。最常见的是WHERE过滤SELECT * FROM person_info WHERE REGEXP_LIKE(mobile, ^1[3-9][0-9]{9}$);如果要在结果里显示标记用CASE WHENSELECT mobile, CASE WHEN REGEXP_LIKE(mobile, ^1[3-9][0-9]{9}$) THEN Y ELSE N END AS mobile_valid FROM person_info;在 PL/SQL 里它可以直接作为布尔条件BEGIN IF REGEXP_LIKE(13800138000, ^1[3-9][0-9]{9}$) THEN DBMS_OUTPUT.PUT_LINE(合法手机号); ELSE DBMS_OUTPUT.PUT_LINE(非法手机号); END IF; END; /注意CASE WHEN里如果一个分支返回N那NULL也会走到ELSE因为REGEXP_LIKE(NULL, ...)的结果是NULL不是真。如果业务上要把空值单独标记就要写成多层判断。这个细节在数据质量报表里很重要别把空值误判成非法值也别把非法值漏掉。4. 常见问题与排查匹配不上、报错、慢查询4.1 明明字符串一样却匹配不上大小写、空格、换行、NULL这类问题我排过太多次。第一步看大小写。REGEXP_LIKE(Oracle, oracle)默认不匹配加i才行。第二步看空格和不可见字符。源数据里可能有前后空格、制表符、回车换行。用DUMP看原始字节最直接SELECT DUMP(mobile, 1016) FROM person_info WHERE ROWNUM 1;第三步看换行。.默认不匹配换行符如果文本是多行要加n。第四步看NULL。NULL不会让WHERE条件成立也不会在CASE WHEN里走THEN。如果字段允许为空最好显式处理SELECT * FROM person_info WHERE mobile IS NOT NULL AND REGEXP_LIKE(TRIM(mobile), ^1[3-9][0-9]{9}$);还有一个容易忽略的点Oracle 里空字符串等于NULL所以REGEXP_LIKE(, ^$)并不会按你想象的方式返回真。要判断空串通常直接用IS NULL。4.2 ORA-12728 等正则报错怎么修ORA-12728: invalid regular expression是正则写错时最常见的报错。典型原因有几种报错原因错误写法修正方向使用了非贪婪量词.*?改成贪婪或换思路使用了前瞻后顾(?...)、(?!...)Oracle 多数版本不支持拆条件使用了非捕获分组(?:...)改成普通分组(...)括号不配对(abc检查左右括号方括号不配对[abc检查左右方括号转义不对\.在某些客户端被吃掉用q[...]或确认转义排查时最实用的办法是把模式单独拿到dual里试SELECT match AS result FROM dual WHERE REGEXP_LIKE(abc, a(bc);报错会直接告诉你正则不合法。不要在一张大表上反复试错先在dual上把模式调通再放进业务 SQL。4.3 性能排查全表扫描、函数索引与预过滤REGEXP_LIKE放在WHERE里普通 B-tree 索引一般不能直接帮你定位数据因为条件是正则表达式不是简单的前缀等值。数据量小的时候没事几百万行、几千万行时CPU 会明显被正则吃掉。我的优化顺序通常是能用LIKE粗筛的先用LIKE。能先用等值、范围条件缩小的先缩小结果集。正则尽量写简单避免.*套.*。高频固定校验考虑函数索引、物化视图或冗余校验列。比如手机号校验可以先加一个LIKE 1%SELECT * FROM person_info WHERE mobile LIKE 1% AND REGEXP_LIKE(mobile, ^1[3-9][0-9]{9}$);如果mobile上有普通索引LIKE 1%有机会走索引范围扫描先把大部分不合格数据挡掉再对剩余行做正则。别看这个写法朴素实际效果往往比单写正则好很多。函数索引要谨慎。Oracle 可以基于表达式建函数索引但你的WHERE REGEXP_LIKE(...)不一定会自动匹配到那个索引。通常需要把表达式写成和索引一致的形式例如基于REGEXP_SUBSTR或CASE WHEN的结果建索引再让查询条件命中。更稳的做法是增加一个校验列在数据入库时就算好查询时直接走该列。空间换时间在大数据量校验场景里往往更划算。4.4 常见问题速查表现象可能原因解决方式明明包含数字却查不到模式没加锚点或写错字符类检查^、$、[0-9]大小写不同匹配失败默认区分大小写加i点号无法匹配换行默认不匹配换行加n多行文本^、$不符合预期默认单行加m长模式难读没有用空白忽略加x并拆行报 ORA-12728用了 Oracle 不支持的正则语法去掉非贪婪、断言、非捕获分组查询慢全表扫描加复杂正则先用 LIKE 粗筛、加冗余列、物化视图空值查不到REGEXP_LIKE(NULL, ...)为 NULL显式处理空值或NVL身份证导出变科学计数法字段是 NUMBER 或导出工具当数值处理改 VARCHAR2导出转文本这张表建议直接收藏遇到问题先按表排查比盲目改正则快得多。5. 我的实操习惯验证、维护、上线5.1 先用 dual 写小样例我写任何正则都不会直接扔进大表 SQL。先在dual里造几条正例、反例、边界例逐条跑通。正例包括正常手机号、带 X 的身份证、最长邮箱反例包括少一位、多一位、中间有空格、全角字符边界例包括空值、纯空格、超长字符串。比如SELECT 正例 AS case_type, 13800138000 AS val FROM dual WHERE REGEXP_LIKE(13800138000, ^1[3-9][0-9]{9}$) UNION ALL SELECT 反例, 12800138000 FROM dual WHERE REGEXP_LIKE(12800138000, ^1[3-9][0-9]{9}$);这样跑一遍正例有结果、反例没结果心里才踏实。直接上大表试错了你都不知道是数据问题还是正则问题。5.2 把复杂正则拆成可维护的表达式复杂正则最怕后人接手。我的习惯是把身份证、邮箱这类长模式拆成几个部分理解必要的话在 SQL 里用x参数换行写或者干脆封成 PL/SQL 函数。比如身份证校验可以拆成地区码、出生日期、顺序码、校验位四段每一段单独测试。不要写一个 200 字符的正则扔在WHERE里三个月后自己都看不懂。如果同一套校验在多个 SQL、多个存储过程里重复出现我会把它定义成包里的常量或者建一个校验函数。比如is_mobile、is_id_card_format这类小函数调用处清晰修改规则时也只改一个地方。性能要求极高的场景再考虑冗余校验列而不是让每个查询都跑一遍长正则。5.3 敏感字段与导出场景的注意点手机号、身份证、银行卡号都属于敏感信息能脱敏展示就不要明文展示。校验归校验查询结果里最好只返回中间四位或者哈希值。导出时更要小心别把全量敏感数据直接导到个人电脑。技术上前面说的科学计数法问题用VARCHAR2和文本导出解决流程上导出权限、审批、脱敏规则要提前定好。我个人的习惯是涉及身份证、手机号的表字段类型一律VARCHAR2长度留足入口处就做正则校验不等到导出时才发现问题。写REGEXP_LIKE时先在dual里把正例、反例、边界例跑一遍再考虑性能优化。这个小习惯帮我省掉过很多次返工尤其是身份证这种最后一位可能是 X 的字段不跑边界例真的很容易漏。
1. 先看语法和定位REGEXP_LIKE 不是普通函数而是条件表达式前几天帮人排一条校验 SQLWHERE里写了 Oracle 的REGEXP_LIKE正则表达式看起来没问题结果就是不对。这个场景在 Oracle 数据库里太常见了用RegExp_Like做手机号、身份证、邮箱、编码格式校验写起来很爽踩坑也很集中。它不是普通函数而是一个条件表达式所以放的位置、返回值、和LIKE的取舍都跟普通字符串函数不一样。适合已经会写基础 SQL、准备把校验逻辑从应用层往数据库层挪一挪的人看也适合刚接触 Oracle 正则表达式、想快速搞明白常用写法的读者。1.1 语法骨架与返回值REGEXP_LIKE的基础语法只有三个参数REGEXP_LIKE(source_string, pattern [, match_parameter])source_string是要检查的源字符串可以是表里的列、绑定变量、表达式甚至是拼接出来的字符串。pattern是正则表达式模式。match_parameter是可选匹配参数用来控制大小写、换行、多行、空白忽略等行为。它的返回值是布尔类型在 SQL 里通常只出现在WHERE、HAVING或CASE WHEN的条件位置在 PL/SQL 里可以直接放进IF判断。这里有个新手特别容易犯的错在 SQL 查询列表里直接SELECT REGEXP_LIKE(abc, a) FROM dual。这在 Oracle SQL 中是不行的因为REGEXP_LIKE是条件不是标量函数。想要看结果得包一层CASE WHENSELECT CASE WHEN REGEXP_LIKE(13800138000, ^1[3-9][0-9]{9}$) THEN 合法 ELSE 非法 END AS is_mobile FROM dual;还有一个细节是NULL。如果source_string是NULLREGEXP_LIKE的结果也是NULL而WHERE只保留结果为真的行所以空值不会被查出来。真实业务里如果字段允许为空最好用NVL兜一下或者单独把空值排除逻辑写清楚否则你会得到一种“明明有数据却查不到”的错觉。1.2 哪些场景该用 REGEXP_LIKE哪些别硬上LIKE和REGEXP_LIKE最核心的区别是LIKE只有两个通配符%表示任意多个字符_表示任意一个字符REGEXP_LIKE背后是正则引擎能表达字符集合、次数、分组、或关系、锚点这些复杂规则。简单前缀、后缀、包含查询我优先用LIKE因为写法简单执行计划也更容易友好。只有碰到“必须是 11 位手机号”“身份证格式必须符合某段规则”“邮箱必须包含 和域名后缀”“金额最多两位小数”这类强格式校验才轮到正则上场。举个实际对比例子。判断字段以A开头LIKE A%就够了没必要写REGEXP_LIKE(col, ^A)。判断字段是不是由 13 位纯数字组成LIKE写不出来这时候用REGEXP_LIKE(col, ^[0-9]{13}$)才顺理成章。再比如判断逗号分隔的数字编号列表LIKE只能粗糙地看有没有逗号正则可以写成^[0-9](,[0-9])*$把空项、多余逗号、非数字字符一起挡住。对比项LIKEREGEXP_LIKE通配能力%、_完整正则元字符、量词、分组、字符类写法复杂度低中到高执行成本通常较低通常较高复杂模式可能明显耗 CPU索引友好度前缀匹配可能走索引普通 B-tree 索引一般不能直接走适合场景简单模糊查询格式校验、复杂模式判断所以我的原则很直接能用LIKE或SUBSTR解决的问题不为了炫技写正则必须用正则时也尽量先用LIKE做粗筛再用REGEXP_LIKE做精筛。数据量小的时候感觉不出来数据量一大正则写不好就是慢查询制造机。1.3 match_parameter 五个开关i、c、n、m、xmatch_parameter是REGEXP_LIKE的第三个参数可以组合使用。很多人只记得i其实另外几个在排查问题时很关键。参数含义常用场景i不区分大小写邮箱、编码、英文名称校验c区分大小写默认行为需要严格区分大小写的编码n让点号.可以匹配换行符多行文本中包含判断m多行模式^和$匹配每行开头结尾文本按行处理x忽略模式中的空白字符把长正则拆成多行写清楚实测一个小例子SELECT match AS result FROM dual WHERE REGEXP_LIKE(Oracle, oracle, i);如果去掉i这条查询就查不出结果因为默认区分大小写。再比如x参数适合把复杂正则写成可读性更好的形式SELECT match AS result FROM dual WHERE REGEXP_LIKE( 13800138000, ^ 1 [3-9] [0-9]{9} $, x );x会忽略模式里的空白所以上面这种写法在逻辑上等价于^1[3-9][0-9]{9}$。我平时写特别长的身份证、邮箱正则时喜欢用x过两个月回头看也能看懂。但要注意字符类里面的空白是否被忽略不同写法下需要验证最稳妥的方式还是在dual里跑一遍正例和反例再上生产。2. Oracle 正则元字符与字符类写模式前先把这些吃透2.1 锚点、量词、分组与转义Oracle 正则表达式里最常用的元字符其实不多但每一个都容易写错。^表示字符串开头$表示字符串结尾。如果不加锚点REGEXP_LIKE(abc123, [0-9])也会返回真因为它只要求“包含一个数字”而不是“整个字符串都是数字”。这就是很多人校验失败的第一原因模式没有首尾锚定。常用元字符整理如下元字符作用示例^匹配开头^A表示以 A 开头$匹配结尾Z$表示以 Z 结尾.匹配任意一个字符默认不含换行a.c可匹配 abc*前一个字符出现 0 次或多次ab*匹配 a、ab、abb前一个字符出现 1 次或多次ab匹配 ab、abb?前一个字符出现 0 次或 1 次colou?r匹配 color、colour{m,n}前一个字符出现 m 到 n 次[0-9]{4,6}[]字符集合[abc]匹配 a、b、c 之一[^]取反字符集合[^0-9]匹配非数字或关系()分组(ab)匹配 ab、abab转义也要单独说。Oracle 字符串里单引号需要写成两个单引号模式里的反斜杠在 SQL 字符串中通常不用像 MySQL 那样双写。但不同客户端、不同脚本工具可能对反斜杠有额外处理。我的习惯是复杂模式用q[...]引用SELECT match AS result FROM dual WHERE REGEXP_LIKE(a.b, q[a\.b]);q[...]的好处是里面的单引号不用双写反斜杠也不容易和客户端转义打架。比如模式里要匹配OBrien这类文本用普通字符串要写OBrien用q[OBrien]就清楚很多。2.2 POSIX 字符类与 Perl 风格简写怎么选Oracle 正则基于 POSIX 风格同时支持一部分 Perl 风格简写。常用的 POSIX 字符类包括字符类含义[[:digit:]]数字[[:alpha:]]字母[[:alnum:]]字母和数字[[:upper:]]大写字母[[:lower:]]小写字母[[:space:]]空白字符[[:punct:]]标点符号[[:xdigit:]]十六进制字符Perl 风格简写里\d表示数字\D表示非数字\w表示单词字符\W表示非单词字符\s表示空白\S表示非空白。这些简写写起来短但我更推荐在正式校验里用 POSIX 字符类比如[0-9]或[[:digit:]]。原因不复杂字符集、NLS 参数、Oracle 版本存在差异时\d这种简写的边界不如显式字符类直观。尤其是迁移环境、导出脚本、跨库比对时显式写[0-9]最不容易出意外。举个例子判断一个字段是不是纯数字SELECT match AS result FROM dual WHERE REGEXP_LIKE(123456, ^[0-9]$);如果要判断字母数字混合可以写SELECT match AS result FROM dual WHERE REGEXP_LIKE(ABC123, ^[[:alnum:]]$);字符类里的^表示取反但它必须写在方括号里面的第一个位置比如[^0-9]。如果写成[0-9^]那就是数字或^字符意思完全变了。这个细节非常小但排查时很折磨人。2.3 贪婪匹配、非贪婪和回溯别让.*成为慢查询元凶Oracle 正则默认是贪婪匹配。.*会尽可能多地匹配字符遇到后续模式失败时再回溯。短字符串无所谓长文本、大表、复杂模式组合在一起回溯就会让 CPU 飙升。比如^.*[0-9].*$这种模式看起来能判断“包含数字”实际上绕了一大圈完全可以用[0-9]或^.*[0-9].*$的简化版。更好的是先明确业务到底要全串匹配还是包含匹配。需要特别提醒的是Oracle 的 POSIX 正则多数版本不支持 PCRE 里的非贪婪写法.*?也不支持前瞻、后顾、非捕获分组这类高级语法。你如果从 Python、JavaScript、PCRE 文档里直接抄一个(?...)、(?!...)、(?:...)在 Oracle 里很可能直接报ORA-12728: invalid regular expression。这不是你 SQL 写错了而是 Oracle 正则引擎不认这些语法。真需要复杂断言逻辑通常得拆成多个条件或者放到应用层、PL/SQL 里用多步判断解决。我踩过的一个坑是为了匹配“不包含某段字符”的字符串直接写了一个带负向断言的正则在测试工具里跑得好好的一放进 Oracle 就报错。后来改成先匹配包含模式再用NOT REGEXP_LIKE反向排除逻辑一样兼容性还更好。正则不是越长越厉害能让维护的人一眼看懂才是生产环境里的好正则。3. 高频业务校验实操手机号、身份证、邮箱、金额3.1 手机号与纯数字11位和13位数字怎么写手机号校验是热词里出现频率最高的场景之一。中国大陆手机号常见规则是 11 位第一位是 1第二位是 3 到 9后面 9 位数字。模式可以写成^1[3-9][0-9]{9}$实测SELECT 合法 AS result FROM dual WHERE REGEXP_LIKE(13800138000, ^1[3-9][0-9]{9}$);如果要匹配“13 位纯数字”比如某些内部编号、订单号、设备号模式就是^[0-9]{13}$或者^\d{13}$虽然手机号通常是 11 位但热词里“13位数字手机号码正则表达式怎么写”这个问法本质上是两个问题手机号规则是手机号规则13 位数字是长度规则。不要把两者混在一起。手机号有号段限制纯数字编号通常只需要长度和字符范围。如果你要校验的是“带国际区号的号码”那又是另一套规则最好先明确数据来源和格式再决定是否允许、空格、连字符。这里还有一个常见错误很多人写^1[3-9]\d{9}$之后发现有些号码查不出来。先检查源字段里有没有空格、换行、不可见字符再检查是不是用了全角数字。用TRIM处理前后空格SELECT 合法 AS result FROM dual WHERE REGEXP_LIKE(TRIM( 13800138000 ), ^1[3-9][0-9]{9}$);但如果字符串中间有空格TRIM是没用的得先用REPLACE去掉或者单独排查。校验 SQL 最怕的就是源数据不干净正则写得再对也没用。3.2 身份证号格式校验与科学计数法导出问题18 位身份证号的格式校验也是高频需求。只做格式校验不校验校验位的话可以用下面这个模式^[1-9][0-9]{5}(18|19|20)[0-9]{2}(0[1-9]|1[0-2])(0[1-9]|[12][0-9]|3[01])[0-9]{3}[0-9Xx]$这个模式拆开看就是前 6 位地区码不能以 0 开头中间 8 位是出生日期年份限定 18、19、20 开头月份 01 到 12日期 01 到 31 的粗略范围后 4 位里最后一位可以是数字或 X。注意它只做格式判断真实身份证校验位算法要复杂得多需要在应用层或 PL/SQL 里单独实现。热词里提到“oracle 数据库 SQL 导出的身份证信息是科学计数法怎么正确显示身份信息”这个坑我在实际项目里见过很多次。根本原因通常是身份证字段被建成了NUMBER类型导出到 Excel、CSV 或某些客户端时长数字被当成数值显示成科学计数法。更严重的是如果字段是NUMBER而数据里又包含 X那根本存不进去所以身份证、银行卡号、手机号这类字段表结构层面就应该用VARCHAR2。如果历史表里已经是NUMBER导出时至少要先转成字符串SELECT TO_CHAR(id_card, FM000000000000000000) AS id_card_text FROM person_info;FM去掉前导空格0格式保证补零。但如果原始数据已经因为数值精度或 X 丢失了那就不是显示问题而是数据损坏问题只能从源头修复。导出成 CSV 时还可以在值后面拼一个制表符或者要求导出工具把该列当文本处理SELECT id_card || CHR(9) AS id_card_text FROM person_info;用CHR(9)拼制表符是一种土办法但很多导出场景确实管用。更正规的方案是导出时选择“文本格式”或者直接生成 Excel 可识别的文本字段。别等到运营拿着科学计数法的表格来找你才发现字段类型一开始就选错了。3.3 邮箱、日期、金额的校验模式邮箱校验用正则很常见但要注意邮箱规则本身非常复杂严格符合标准的邮箱格式可能长到没人愿意维护。业务系统里通常用中等严格模式就够了^[A-Za-z0-9._%-][A-Za-z0-9.-]\.[A-Za-z]{2,}$实测SELECT 合法 AS result FROM dual WHERE REGEXP_LIKE(test.userexample.com, ^[A-Za-z0-9._%-][A-Za-z0-9.-]\.[A-Za-z]{2,}$);如果希望域名部分不区分大小写可以加iSELECT 合法 AS result FROM dual WHERE REGEXP_LIKE(Test.UserExample.COM, ^[A-Za-z0-9._%-][A-Za-z0-9.-]\.[A-Za-z]{2,}$, i);日期格式校验也很实用。比如要求YYYY-MM-DD^[0-9]{4}-[0-9]{2}-[0-9]{2}$这个只能校验形状不能校验 2 月 30 日这种无效日期。更严格的日期合法性可以结合TO_DATE和异常捕获处理或者用正则先粗筛再让数据库日期函数判断。金额校验可以写成^[0-9](\.[0-9]{1,2})?$它允许整数或者最多两位小数不允许负数、千分位、货币符号。如果业务允许负数就改成^-?[0-9](\.[0-9]{1,2})?$逗号分隔的数字列表也可以顺手判断^[0-9](,[0-9])*$这个模式能挡住空项、连续逗号、首尾逗号。像oracle按逗号拆分列为多行这类需求通常先用REGEXP_LIKE判断格式再用REGEXP_SUBSTR配合层级查询拆行。校验和拆分最好分开做不要在一个表达式里塞太多逻辑。3.4 在 WHERE、CASE WHEN、PL/SQL 里的不同写法同一个正则在不同位置写法不同。最常见的是WHERE过滤SELECT * FROM person_info WHERE REGEXP_LIKE(mobile, ^1[3-9][0-9]{9}$);如果要在结果里显示标记用CASE WHENSELECT mobile, CASE WHEN REGEXP_LIKE(mobile, ^1[3-9][0-9]{9}$) THEN Y ELSE N END AS mobile_valid FROM person_info;在 PL/SQL 里它可以直接作为布尔条件BEGIN IF REGEXP_LIKE(13800138000, ^1[3-9][0-9]{9}$) THEN DBMS_OUTPUT.PUT_LINE(合法手机号); ELSE DBMS_OUTPUT.PUT_LINE(非法手机号); END IF; END; /注意CASE WHEN里如果一个分支返回N那NULL也会走到ELSE因为REGEXP_LIKE(NULL, ...)的结果是NULL不是真。如果业务上要把空值单独标记就要写成多层判断。这个细节在数据质量报表里很重要别把空值误判成非法值也别把非法值漏掉。4. 常见问题与排查匹配不上、报错、慢查询4.1 明明字符串一样却匹配不上大小写、空格、换行、NULL这类问题我排过太多次。第一步看大小写。REGEXP_LIKE(Oracle, oracle)默认不匹配加i才行。第二步看空格和不可见字符。源数据里可能有前后空格、制表符、回车换行。用DUMP看原始字节最直接SELECT DUMP(mobile, 1016) FROM person_info WHERE ROWNUM 1;第三步看换行。.默认不匹配换行符如果文本是多行要加n。第四步看NULL。NULL不会让WHERE条件成立也不会在CASE WHEN里走THEN。如果字段允许为空最好显式处理SELECT * FROM person_info WHERE mobile IS NOT NULL AND REGEXP_LIKE(TRIM(mobile), ^1[3-9][0-9]{9}$);还有一个容易忽略的点Oracle 里空字符串等于NULL所以REGEXP_LIKE(, ^$)并不会按你想象的方式返回真。要判断空串通常直接用IS NULL。4.2 ORA-12728 等正则报错怎么修ORA-12728: invalid regular expression是正则写错时最常见的报错。典型原因有几种报错原因错误写法修正方向使用了非贪婪量词.*?改成贪婪或换思路使用了前瞻后顾(?...)、(?!...)Oracle 多数版本不支持拆条件使用了非捕获分组(?:...)改成普通分组(...)括号不配对(abc检查左右括号方括号不配对[abc检查左右方括号转义不对\.在某些客户端被吃掉用q[...]或确认转义排查时最实用的办法是把模式单独拿到dual里试SELECT match AS result FROM dual WHERE REGEXP_LIKE(abc, a(bc);报错会直接告诉你正则不合法。不要在一张大表上反复试错先在dual上把模式调通再放进业务 SQL。4.3 性能排查全表扫描、函数索引与预过滤REGEXP_LIKE放在WHERE里普通 B-tree 索引一般不能直接帮你定位数据因为条件是正则表达式不是简单的前缀等值。数据量小的时候没事几百万行、几千万行时CPU 会明显被正则吃掉。我的优化顺序通常是能用LIKE粗筛的先用LIKE。能先用等值、范围条件缩小的先缩小结果集。正则尽量写简单避免.*套.*。高频固定校验考虑函数索引、物化视图或冗余校验列。比如手机号校验可以先加一个LIKE 1%SELECT * FROM person_info WHERE mobile LIKE 1% AND REGEXP_LIKE(mobile, ^1[3-9][0-9]{9}$);如果mobile上有普通索引LIKE 1%有机会走索引范围扫描先把大部分不合格数据挡掉再对剩余行做正则。别看这个写法朴素实际效果往往比单写正则好很多。函数索引要谨慎。Oracle 可以基于表达式建函数索引但你的WHERE REGEXP_LIKE(...)不一定会自动匹配到那个索引。通常需要把表达式写成和索引一致的形式例如基于REGEXP_SUBSTR或CASE WHEN的结果建索引再让查询条件命中。更稳的做法是增加一个校验列在数据入库时就算好查询时直接走该列。空间换时间在大数据量校验场景里往往更划算。4.4 常见问题速查表现象可能原因解决方式明明包含数字却查不到模式没加锚点或写错字符类检查^、$、[0-9]大小写不同匹配失败默认区分大小写加i点号无法匹配换行默认不匹配换行加n多行文本^、$不符合预期默认单行加m长模式难读没有用空白忽略加x并拆行报 ORA-12728用了 Oracle 不支持的正则语法去掉非贪婪、断言、非捕获分组查询慢全表扫描加复杂正则先用 LIKE 粗筛、加冗余列、物化视图空值查不到REGEXP_LIKE(NULL, ...)为 NULL显式处理空值或NVL身份证导出变科学计数法字段是 NUMBER 或导出工具当数值处理改 VARCHAR2导出转文本这张表建议直接收藏遇到问题先按表排查比盲目改正则快得多。5. 我的实操习惯验证、维护、上线5.1 先用 dual 写小样例我写任何正则都不会直接扔进大表 SQL。先在dual里造几条正例、反例、边界例逐条跑通。正例包括正常手机号、带 X 的身份证、最长邮箱反例包括少一位、多一位、中间有空格、全角字符边界例包括空值、纯空格、超长字符串。比如SELECT 正例 AS case_type, 13800138000 AS val FROM dual WHERE REGEXP_LIKE(13800138000, ^1[3-9][0-9]{9}$) UNION ALL SELECT 反例, 12800138000 FROM dual WHERE REGEXP_LIKE(12800138000, ^1[3-9][0-9]{9}$);这样跑一遍正例有结果、反例没结果心里才踏实。直接上大表试错了你都不知道是数据问题还是正则问题。5.2 把复杂正则拆成可维护的表达式复杂正则最怕后人接手。我的习惯是把身份证、邮箱这类长模式拆成几个部分理解必要的话在 SQL 里用x参数换行写或者干脆封成 PL/SQL 函数。比如身份证校验可以拆成地区码、出生日期、顺序码、校验位四段每一段单独测试。不要写一个 200 字符的正则扔在WHERE里三个月后自己都看不懂。如果同一套校验在多个 SQL、多个存储过程里重复出现我会把它定义成包里的常量或者建一个校验函数。比如is_mobile、is_id_card_format这类小函数调用处清晰修改规则时也只改一个地方。性能要求极高的场景再考虑冗余校验列而不是让每个查询都跑一遍长正则。5.3 敏感字段与导出场景的注意点手机号、身份证、银行卡号都属于敏感信息能脱敏展示就不要明文展示。校验归校验查询结果里最好只返回中间四位或者哈希值。导出时更要小心别把全量敏感数据直接导到个人电脑。技术上前面说的科学计数法问题用VARCHAR2和文本导出解决流程上导出权限、审批、脱敏规则要提前定好。我个人的习惯是涉及身份证、手机号的表字段类型一律VARCHAR2长度留足入口处就做正则校验不等到导出时才发现问题。写REGEXP_LIKE时先在dual里把正例、反例、边界例跑一遍再考虑性能优化。这个小习惯帮我省掉过很多次返工尤其是身份证这种最后一位可能是 X 的字段不跑边界例真的很容易漏。
/LR分析实战)
