1. 从一次数据清洗开始LIKE 到底卡在哪前阵子接手一个遗留库的清洗任务用户表里有个contact字段当初设计的时候没做任何校验客户在录入界面里什么都往里塞手机号、座机、邮箱、微信号甚至还有请发短信联系下班后勿扰这种整句备注。业务方现在的诉求很明确把这批数据里合法的手机号挑出来做触达我第一反应是写LIKE加几个通配符写完跑了一遍结果里混进了一堆 13 位纯数字的订单号反过来又漏掉了一批带全角空格前缀的号码。这种活儿在 Oracle 数据库里真正好用的工具是RegExp_Like也就是 Oracle 内置的正则表达式匹配函数它把 POSIX 风格的正则能力直接塞进了 SQL 层不用把数据拉到应用层再过滤一遍。这篇内容我会把 RegExp_Like 的完整参数、Oracle 正则表达式里哪些语法能用哪些不能用、几类常见字段的校验写法、性能代价以及我自己踩过的坑一次讲透刚入门的同学可以照着抄写过几次但总在细节上翻车的同学也能找到几个平时不太会注意的点。1.1 LIKE 在真实数据面前的三种失效形态很多人觉得LIKE够用是因为测试库里的数据太干净了。真实库里LIKE大概会在三个地方失效。第一个是位置不固定contact LIKE %1[3-9]%这种写法在 Oracle 里根本不成立因为方括号只是普通字符LIKE只有%和_两个通配符想表达1 后面跟 3 到 9 之间的任意一个数字它完全没有办法。第二个是长度无法约束LIKE 1%会同时命中 11 位手机号和 13 位的订单号、18 位的流水号。你可以用LENGTH(contact) 11补救但一旦数据里混入空格、换行、制表符LENGTH就会骗你比如尾部多一个空格也占一位。第三个是字符集范围表达不了想筛全部由数字组成的字段LIKE需要把 0 到 9 一个个列出来写成LIKE [0-9]%——注意它在 Oracle 里依然是字面匹配不是在表达范围。真要做到只允许数字LIKE基本无解。而这三件事恰恰是REGEXP_LIKE的日常。1.2 RegExp_Like 到底是什么它不做什么REGEXP_LIKE(source_string, pattern, match_parameter)是 Oracle 从 10g 开始引入的正则家族成员之一同族的还有REGEXP_INSTR、REGEXP_SUBSTR、REGEXP_REPLACE11g 又补了一个REGEXP_COUNT。它的职责非常单一判断源字符串里能不能找到匹配给定模式的片段返回一个布尔值。这里有个特别容易被误解的点REGEXP_LIKE不是完全匹配它是包含匹配。REGEXP_LIKE(abc123def, \d)会返回 TRUE因为中间有数字。你如果想让整个字段严格符合某个格式必须自己加锚点也就是^和$写成REGEXP_LIKE(col, ^\d$)。我见过太多人在校验逻辑里漏掉这两个符号导致含有一个数字就打勾的宽松判断被当成严格校验上线。另一个要提前说清楚的是返回值在 SQL 和 PL/SQL 里的差异。在 PL/SQL 里你可以直接IF REGEXP_LIKE(v_str, ^\d$) THEN因为它返回的是BOOLEAN但在纯 SQL 里SELECT REGEXP_LIKE(col,...) FROM t是不成立的SQL 层没有布尔类型可以往外投影会直接报操作符相关的错误不同版本提示不完全一致常见的是 ORA-00920 一类。想在查询里看到结果必须包一层CASE WHEN REGEXP_LIKE(...) THEN 1 ELSE 0 END。1.3 什么场景下别硬上正则正则不是免费的。只要WHERE里出现了REGEXP_LIKEOracle 就基本放弃了对该列普通 B-Tree 索引的直接使用除非你专门建函数索引或者虚拟列索引。所以判断标准很简单能用等值、范围、LIKE前缀匹配解决的就别用正则。举个具体例子如果要筛订单号以SO开头的记录WHERE order_no LIKE SO%能走索引而WHERE REGEXP_LIKE(order_no, ^SO)就走不了。数据量在几百万行以上的表两者的差距是分钟级和秒级的差别。正则真正值得用的地方是那些格式复杂、位置不固定、字符类需要范围表达的判断比如手机号、证件号、邮箱、IP、带千分位的金额串以及从一坨自由文本里捞特定片段。注意把正则当成提速手段是新手的经典误区。它是表达能力的补充不是性能的补充两者的定位别搞反。2. REGEXP_LIKE 参数逐个拆别只记住前两个大部分人用REGEXP_LIKE时只写前两个参数第三个match_parameter常年空着然后在小写字母匹配、多行文本、长模式可读性这三件事上反复栽跟头。这一节把三个参数掰开说顺便说说 Oracle 对模式串长度和 NLS 环境的隐形依赖。2.1 source_string 能放什么以及隐式转换的暗坑第一个参数是源字符串可以是一个列、一个字面量、一个表达式甚至一个函数返回值。看起来没什么可讲的但坑就在类型上。如果这一列是NUMBER或者DATE类型Oracle 会先做隐式转换再交给正则处理而隐式转换的结果依赖当前会话的NLS_NUMERIC_CHARACTERS和NLS_DATE_FORMAT。我遇到过一起真实事故测试环境里REGEXP_LIKE(amount_col, ^\d(\.\d{2})?$)跑得好好的上线后一批数据突然判断不通过查了半天发现生产库的会话 NLS 设置里小数点是逗号123.45从数值转成字符串之后变成了123,45。这类问题不会报错只会静默地给出错误结果最难查。所以我的第一条建议是做格式校验时源参数尽量先显式转换成字符串比如TO_CHAR(amount_col, FM999999990.00)或者干脆在业务上保证这一列本身就是VARCHAR2。校验格式的活儿交给字符型字段数值型字段的合法性应该靠数据类型和约束去保证各司其职。另外CLOB字段也可以直接传给REGEXP_LIKEOracle 会隐式处理但超大 CLOB 上的正则非常吃 CPU动辄上千字节的模式在几万行 CLOB 上跑性能会难看到你想砸键盘。2.2 pattern 的书写规则与长度天花板第二个参数是模式串一个标准的正则表达式。Oracle 用的是 POSIX 正则的扩展版本另外混入了一部分 Perl 风格的简写。它在 SQL 里就是一个普通字符串字面量注意SQL 层不处理反斜杠转义所以\d直接写就行不需要写成\\d。这一点和 Java、Python 的字符串字面量习惯不一样从应用层切过来的人经常多敲一层反斜杠结果匹配不到东西。长度上有个硬限制官方文档给出的说明是模式串不能超过512 字节超出了会报ORA-12733: regular expression too long。512 字节看着不少但如果你打算把一整段业务规则用|拼成一坨巨型正则很容易就顶到天花板。踩过这个坑之后我的习惯是拆分——把复合规则拆成几个独立的REGEXP_LIKE用AND/OR连接虽然写起来啰嗦但可读性和可维护性都好得多出问题的时候也能一眼定位是哪条规则挂了。还有一点关于大小写模式串里的字母默认是区分大小写的写^abc匹配不到ABC。想让整个模式忽略大小写靠第三个参数别去用UPPER()包字段那样等于给列加了一层函数索引彻底废掉。2.3 match_parameter 的五个取值与组合顺序第三个参数是匹配模式取值一共五个可以组合比如im取值含义典型用途c区分大小写默认行为明确表达意图一般不用写i不区分大小写邮箱、英文编码类字段n让.能匹配换行符处理跨行的备注文本m多行模式^$匹配每行首尾一段文本里逐行找模式x忽略模式中的空白字符把长正则分行排版便于阅读i和c同时出现时后写的那个生效ic相当于区分大小写ci相当于不区分。这个规则文档里有写但很少有人注意写错了排查起来相当费劲因为从结果上看只是某些行匹配某些行不匹配。x参数是我个人最喜欢的一个它允许你把正则当代码一样排版。比如一个 18 位证件号的校验写成一整行是七十多个字符眼睛都花了加上x之后可以这样写SELECT 1 FROM DUAL WHERE REGEXP_LIKE(11010519900307123X, ^[1-9] \d{5} (18|19|20)\d{2} (0[1-9]|1[0-2]) (0[1-9]|[12]\d|3[01]) \d{3} [\dXx]$, x);每个片段单独一行分组在哪、量词管谁一眼就清楚。注意x模式下空格和换行都会被忽略所以如果模式里真的要匹配一个空格必须写成[ ]或者\s这是个使用x参数时最常翻车的地方。最后一个不能忽视的东西是NLS 环境对正则的隐性影响。[:alpha:]、[:upper:]、[:lower:]这些 POSIX 字符类判断字母的方式依赖当前会话的排序规则设置在不同 NLS 配置下同一个模式对同一个字符的判断结果可能不一样。跨库迁移脚本的时候这类问题特别隐蔽。3. 元字符速查Oracle 正则里哪些能用、哪些不能用Oracle 的正则引擎不是完整版 PCRE它有自己的取舍。从别的语言切过来的人第一件事通常是被\u4e00、\b、(?:...)这几个写法各坑一次。这一节就把能用的整理清楚把不能用的单独列出来省得你一个个去试。3.1 锚点、量词、字符类这三大件锚点只有四个^表示字符串或行开头$表示结尾。它们本身是零宽的不消耗字符。^$空串检测、^abc$严格匹配整串是最常用的两种组合。量词有六种*0 次或多次、1 次或多次、?0 次或 1 次、{n}恰好 n 次、{n,}至少 n 次、{n,m}n 到 m 次。这里有个经典陷阱叫贪婪匹配默认情况下量词会尽可能地多吃字符。比如REGEXP_SUBSTR(ab, .*)返回的是整个ab而不是你想要的a。想让它吃到第一个就停得用非贪婪写法.*?。Oracle 支持非贪婪量词这一点比它不支持的一些语法要良心得多。字符类用方括号表达方括号里是或的关系。[abc]匹配 a、b、c 中任意一个[a-z]是范围[^0-9]是取反注意^在方括号里表示取反在方括号外表示开头位置不同含义完全不一样。方括号里的特殊字符大部分会失去特殊含义比如[.]就是字面点号不需要转义但]、-、^这三个位置敏感要放在合适的地方或者转义。3.2 分组、反向引用与或运算圆括号的作用是分组把一段模式打包成一个整体然后给它加量词比如(abc)表示abc重复一到多次。同时它也是捕获组可以被后面的反向引用复用。反向引用用\1到\9表示指的是第几个捕获组刚刚匹配到的内容。这个能力在处理重复结构时非常好用比如找形如abc123abc这种前后重复的串SELECT 1 FROM DUAL WHERE REGEXP_LIKE(abc123abc, ^(\w)\d\1$);\1会要求末尾那一段和第一组匹配到的一模一样abc123abd就匹配不上。ORA-12727 这个报错就是反向引用用错了比如引用了根本不存在的组号。竖线|是或优先级在所有运算符里最低所以^abc|xyz$实际上是(^abc)|(xyz$)前后两个锚点各管各的很多人想表达以 abc 开头或以 xyz 结尾写出来却误以为自己写的是abc 开头且 xyz 结尾。要加括号明确意图。3.3 POSIX 字符类与 Perl 简写的取舍Oracle 里关于数字、字母、空白有两套写法都能用但各有各的坑。Perl 简写\d数字、\D非数字、\w字母数字下划线、\W取反、\s空白字符、\S非空白。写法短用起来爽写手机号、订单号这类判断首选\d。POSIX 字符类写法是双层的方括号[[:digit:]]、[[:alpha:]]、[[:alnum:]]、[[:space:]]、[[:upper:]]、[[:lower:]]、[[:punct:]]、[[:xdigit:]]。这里新手最常犯的错是写成[:digit:]这实际上是匹配冒号、d、i、g、t 这几个字符里的任意一个语法上不报错结果完全不对还特别难发现。那什么时候用哪套我的习惯是能用\d的地方就用\d因为它对 NLS 不敏感行为稳定涉及字母大小写判断、需要跟字符集语义绑定的场景才用 POSIX 类并且必须在本库实测。比如判断一个字符串里有没有汉字常见写法是REGEXP_LIKE(col, [[:alpha:]])在 UTF-8 字符集的库里汉字通常会被归入[:alpha:]但这个行为受 NLS 参数影响不能想当然。更稳的老办法是拿LENGTH和LENGTHB的差值判断——多字节字符会让字节长度大于字符长度SELECT 1 FROM DUAL WHERE LENGTH(张三) LENGTHB(张三);3.4 Oracle 正则与其他引擎的差异清单下面这些写法在 Python、JavaScript 里是家常便饭在 Oracle 里直接不能用必须换思路写法其他引擎Oracle 里的替代方案\u4e00-\u9fa5匹配汉字区间用[[:alpha:]]配合 NLS 实测或用LENGTH/LENGTHB差值\b单词边界常用不支持用 (^(?:...)非捕获组常用不支持只能用普通捕获组(?...)(?!)断言常用不支持需要靠REGEXP_INSTR配合子串截取绕\x41十六进制转义支持不支持写[A]或直接写字符# 注释部分支持不支持x模式只忽略空白不能加注释被这张表坑过的场景里最麻烦的是断言。有些业务规则比如密码必须包含大小写数字但不含特殊字符在支持断言的语言里一行搞定在 Oracle 里只能拆成多个REGEXP_LIKE用AND连接逐个检查字符类是否出现。4. 直接抄的实战写法校验与清洗理论讲完进入能直接落地的部分。这一节给的都是我在生产里用过、跑过验证的写法你复制粘贴改个列名就能用。写之前有个统一的建议先在DUAL上把你的模式调通再挂到真实列上否则大表上跑一次几千万行出错了连原因都定位不到。4.1 五类最常见字段的校验写法手机号大陆 11 位^1[3-9]\d{9}$。注意1后面是[3-9]把12开头的号段排除在外能挡掉一大批误填数据。顺便解释一下热词里有人搜13 位数字手机号码正则表达式怎么写——手机号本身只有 11 位13 位纯数字的通常是订单号或者老式账号如果你真的需要匹配 13 位纯数字那就是^\d{13}$但别把它叫手机号否则和 11 位规则混在一起容易出乱子。邮箱^[A-Za-z0-9._%-][A-Za-z0-9.-]\.[A-Za-z]{2,}$配合i参数更简洁。这个模式覆盖了绝大多数真实邮箱但它不校验域名是否存在、也不做 RFC 级别的完整合规业务上只要格式能过就够了非要做到严格合规正则的长度和复杂度都会失控。IPv4 地址^((25[0-5]|2[0-4]\d|1\d{2}|[1-9]?\d)\.){3}(25[0-5]|2[0-4]\d|1\d{2}|[1-9]?\d)$。这个模式的精髓在四段分支的排序——先写25[0-5]再写2[0-4]\d最后才是[1-9]?\d。正则的或是从左往右试的把范围大的分支写在前面会导致256被前面的分支吃掉一半255反而匹配失败。这是我一直强调要知其所以然的地方抄之前先看懂顺序为什么这么排。日期^\d{4}-(0[1-9]|1[0-2])-(0[1-9]|[12]\d|3[01])$。它能挡掉2024-13-45这种明显错误但挡不住2024-02-31因为月份和天数的联动关系正则表达不了。这类校验必须交给日期函数正则只负责挡掉形态上的垃圾数据。18 位证件号^[1-9]\d{5}(18|19|20)\d{2}(0[1-9]|1[0-2])(0[1-9]|[12]\d|3[01])\d{3}[\dXx]$。注意中间年份分支(18|19|20)后面跟着\d{2}合起来是 4 位年份最后一位用[\dXx]兼容校验位可能为 X 的情况。这类数据很敏感做校验归做校验别在 SQL 里SELECT出一整列明文到处传。4.2 三种挂载方式WHERE、CASE、CHECK 约束最常见的用法当然是在WHERE里做过滤SELECT user_id, contact FROM user_info WHERE REGEXP_LIKE(TRIM(contact), ^1[3-9]\d{9}$);注意我在外面套了个TRIM。这不是多余的生产数据里前后带空格的情况太常见了不TRIM就会漏掉一批本来合法的号码。有人担心TRIM会破坏索引这个担心是对的所以更彻底的做法是在入库阶段就把空格清掉而不是每次查询都TRIM。第二种是投影出判断结果用于数据质量盘点SELECT CASE WHEN REGEXP_LIKE(TRIM(contact), ^1[3-9]\d{9}$) THEN MOBILE WHEN REGEXP_LIKE(TRIM(contact), ^[A-Za-z0-9._%-][A-Za-z0-9.-]\.[A-Za-z]{2,}$, i) THEN EMAIL ELSE UNKNOWN END AS contact_type, COUNT(*) FROM user_info GROUP BY CASE WHEN REGEXP_LIKE(TRIM(contact), ^1[3-9]\d{9}$) THEN MOBILE WHEN REGEXP_LIKE(TRIM(contact), ^[A-Za-z0-9._%-][A-Za-z0-9.-]\.[A-Za-z]{2,}$, i) THEN EMAIL ELSE UNKNOWN END;CASE里判断是有顺序的把规则写得越具体的放前面越宽松的放后面最后用ELSE兜底这样出来的分类结果才符合直觉。第三种是在建表时直接加CHECK约束从源头拦截脏数据ALTER TABLE user_info ADD CONSTRAINT ck_user_contact CHECK (contact IS NULL OR REGEXP_LIKE(contact, ^1[3-9]\d{9}$));注意CHECK约束里必须显式加IS NULL OR。因为REGEXP_LIKE(NULL, ...)返回的是NULL而不是FALSE而 SQL 的CHECK约束对NULL结果是不违反也就是说空值天然能通过。你如果不加IS NULL判断业务上以为自己做了强制校验实际上字段可以随便留空。4.3 和 REGEXP 家族其他成员打配合REGEXP_LIKE是判断实际清洗过程中往往还要提取和替换这时候同族的另外三个函数就派上用场了。REGEXP_SUBSTR负责从乱糟糟的文本里捞出目标片段。比如备注里写了联系人电话13800138000请工作时间联系直接取手机号SELECT REGEXP_SUBSTR(remark, 1[3-9]\d{9}) FROM user_info;如果一段文本里有多个手机号第三个位置参数指定起始位置、第四个指定第几次出现REGEXP_SUBSTR(remark, 1[3-9]\d{9}, 1, 2)就能取出第二个。REGEXP_REPLACE负责脱敏和格式统一。把手机号中间四位打码SELECT REGEXP_REPLACE(13800138000, (\d{3})\d{4}(\d{4}), \1****\2) FROM DUAL;这里的\1\2是反向引用指的是模式里第一个和第二个捕获组匹配到的内容替换串里直接引用。这是REGEXP_REPLACE最强大的地方比单纯的字符替换灵活太多。想一次性清掉所有空格、制表符、换行SELECT REGEXP_REPLACE(remark, [[:space:]], ) FROM user_info;REGEXP_COUNT用来数出现次数配合REGEXP_SUBSTR做拆分非常合适后面第 6 节会展开讲。这里也顺带回应一个很实际的场景很多人清洗完数据导出成 CSV用 Excel 一打开长数字比如证件号、银行卡号自动变成科学计数法显示看起来像数据被改坏了。其实文件里的内容没变是 Excel 的显示和解析行为导致的。规避方式是把该列用文本限定符包起来导出或者干脆在 Oracle 这一侧就按文本格式生成文件别依赖表格软件去猜列类型。5. 性能与排错正则写错能查一天这一节可能是全文最有价值的部分。正则的写法错误通常分两类一类直接报错好消息是能快速定位另一类是不报错但结果不对这才是真正消耗时间的地方。5.1 为什么你的正则走不了索引前面提过一句这里展开说清楚。WHERE REGEXP_LIKE(col, ^SO)这种写法Oracle 不会自动帮你把^SO转换成LIKE SO%结果是全表扫描。解决办法有三个按推荐程度排序。第一选择拆开写用LIKE做粗筛用正则做精筛。比如筛手机号WHERE contact LIKE 1% AND REGEXP_LIKE(contact, ^1[3-9]\d{9}$);只要contact上有索引LIKE 1%就能把候选集从千万行缩到百万行正则只在这批候选行上计算速度快一个数量级。这个思路的本质是先用便宜的谓词过滤再用贵的谓词精算普适性极强。第二选择虚拟列加索引11g 及以上。把正则判断结果固化成一个虚拟列然后在这个列上建索引ALTER TABLE user_info ADD ( is_mobile AS (CASE WHEN REGEXP_LIKE(contact, ^1[3-9]\d{9}$) THEN 1 ELSE 0 END) ); CREATE INDEX idx_user_is_mobile ON user_info(is_mobile);之后查询时用WHERE is_mobile 1就能命中索引。虚拟列表达式要求是确定性的正则函数在这一点上实测可用但考虑到不同版本的行为差异动手前务必在测试库验一遍再上生产。第三选择函数索引。原理和虚拟列类似只是把表达式写在索引定义里。索引表达式的写法必须和查询里的表达式一字不差多一个空格、少一个TRIM都用不上索引。提示统计信息也很关键。加了虚拟列索引之后记得收集表的统计信息否则优化器可能压根不知道这个索引存在。5.2 报错速查表从 ORA-12725 到 ORA-12733正则相关的报错基本集中在 ORA-127xx 这个区间遇到不用慌对照下表基本能定位报错号含义最常见的真实原因ORA-12725括号不匹配少写或多写了一个(或)多层嵌套时最容易ORA-12726方括号不匹配字符类少了右括号或者把[当字面量用没转义ORA-12727反向引用无效写了\5但模式里只有 2 个捕获组ORA-12728字符类里的范围无效写成了[9-1]这种倒序范围或[a-\d]这种混类型范围ORA-12729字符类名无效写成了[[:digits:]]这种不存在的类名ORA-12733正则表达式过长模式超过 512 字节需要拆成多条规则ORA-00920操作符无效在 SQL 的SELECT列表里直接投影REGEXP_LIKE的布尔结果除此之外还有一种不报错但不对的情况值得单独列出来模式能编译、能执行、结果全错。典型代表就是前面提到的[:digit:]单个方括号和漏写$锚点。这类问题没有报错信息帮你唯一的手段就是缩小测试范围用DUAL逐段验证。5.3 我踩过的坑和一套调试笨办法说几个印象深刻的。第一个坑是空值。我曾经写了一段清洗逻辑判断字段不是合法手机号就标记为待修复上线之后发现几万行空值全被标成了待修复。原因就是REGEXP_LIKE(NULL, ...)返回NULL而NOT NULL也是NULLCASE走不到预期分支。修法是显式判断CASE WHEN contact IS NULL THEN EMPTY WHEN REGEXP_LIKE(...) THEN OK ELSE BAD END。所有跟正则打交道的逻辑第一步都应该是判空。第二个坑是TRIM的位置。我一开始写的是REGEXP_LIKE(TRIM(contact), ...)后来在做虚拟列索引时发现索引表达式和查询表达式里必须都带TRIM只要有一边漏了索引就用不上。而且全角空格TRIM是处理不掉的需要用REGEXP_REPLACE(col, [[:space:]], )先清一遍。第三个坑是大小写。做邮箱校验的时候我用的是^[A-Za-z0-9._%-]...因为手动写了大小写范围所以没加i跑起来没问题。后来有人维护代码时把模式简化成了^[a-z0-9._%-]...忘了加参数导致所有含大写字母的邮箱被判定为非法。这件事之后我定了个规矩只要涉及字母范围要么把大小写都写全要么老老实实加i二选一别混着来。第四个坑是连接符-在方括号里的位置。[A-Za-z0-9._%-]结尾的-我当初是想表达加号和减号两个字面字符但-放在方括号末尾时确实是字面量放在中间就变成范围了。[-]是安全的[-.]就会被解析成从到.的范围。这种位置敏感的字符宁可多转义一次也别赌。至于调试方法我用的是最土但最有效的一招把长正则拆开逐段在DUAL上验证最后再拼回去。先单独测^1[3-9]确认能匹配138、199、159不匹配128、108再测\d{9}$确认长度约束生效最后拼成完整模式用一批正例和反例同时跑。准备一组必须通过和必须不通过的样本比来回改正则快得多。另外我会把最终的测试用例留在注释里下次有人改模式跑一遍就知道有没有改坏。6. 延展从字符串拆分行到批量清洗REGEXP_LIKE本身只做判断但把它和CONNECT BY、REGEXP_SUBSTR、存储过程组合起来能解决一批非常实际的脏活。这一节说三个最常见的延展场景。6.1 逗号分隔串拆成多行老系统里一个字段塞多个值的设计太常见了比如tag_ids里存着3,17,52,108。要把它拆成多行关系数据标准套路是CONNECT BY配合REGEXP_SUBSTRSELECT id, TRIM(REGEXP_SUBSTR(tag_ids, [^,], 1, LEVEL)) AS tag_id FROM article CONNECT BY LEVEL REGEXP_COUNT(tag_ids, [^,]) AND PRIOR id id AND PRIOR SYS_GUID() IS NOT NULL;这里有几个细节必须解释清楚。[^,]的意思是一个或多个非逗号字符它就是拆分时最常用的模式。REGEXP_COUNT(tag_ids, [^,])算出这一行有几个元素作为CONNECT BY的上界。PRIOR id id加上PRIOR SYS_GUID() IS NOT NULL是为了避免层次查询在每行上做笛卡尔积——不加这个条件行与行之间会互相串结果会成倍膨胀这是层次查询的经典陷阱。SYS_GUID()每行返回的值都不同用它保证条件永远为真但又能约束父行关系是社区里流传很广的一个技巧。如果字段里可能存着空字符串或者连续逗号3,,52[^,]会跳过空元素这通常是想要的行为如果确实需要保留空位把模式换成[^,]*但要知道这样处理连续逗号时会多出空行。6.2 在 PL/SQL 里做批量清洗与脱敏判断和拆分都有了接下来就是落地成可重复执行的清洗流程。这类脚本我一般写成存储过程分三阶段标记、修正、复检。CREATE OR REPLACE PROCEDURE sp_clean_contact IS v_total NUMBER : 0; v_bad NUMBER : 0; BEGIN -- 第一阶段标记非法数据 INSERT INTO contact_bad_log(id, raw_value, reason, log_time) SELECT user_id, contact, CASE WHEN contact IS NULL THEN EMPTY WHEN REGEXP_LIKE(TRIM(contact), ^1[3-9]\d{9}$) THEN OK WHEN REGEXP_LIKE(TRIM(contact), ^[A-Za-z0-9._%-][A-Za-z0-9.-]\.[A-Za-z]{2,}$, i) THEN EMAIL ELSE INVALID END, SYSDATE FROM user_info WHERE contact IS NULL OR NOT REGEXP_LIKE(TRIM(contact), ^1[3-9]\d{9}$); -- 第二阶段对能救的数据做统一格式 UPDATE user_info SET contact REGEXP_REPLACE(contact, [[:space:]], ) WHERE REGEXP_LIKE(contact, [[:space:]]); -- 第三阶段脱敏输出手机号中间四位打码 v_bad : SQL%ROWCOUNT; DBMS_OUTPUT.PUT_LINE(处理行数: || v_bad); EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE; END;这段代码里有三个我想强调的实践。第一清洗前先落一份日志表原始值、判定原因、时间戳都留下来。数据清洗最忌讳直接改原表还不留痕出事之后无法回溯。第二批量更新要分批提交表大了之后一个UPDATE会撑爆回滚段实际生产里一般是按主键区间分片循环处理。第三脱敏逻辑和数据清洗要分开做别在同一个步骤里既改数据又打码否则一旦出错你连原始数据都找不回来。还有一点清洗过程的 SQL 如果是要定期跑的会话的 NLS 参数一定要在脚本开头固定下来避免因为连进来的客户端设置不同导致判断结果漂移。这是跨环境运行正则脚本时最隐蔽的一类问题。6.3 版本差异与兼容性提示REGEXP_LIKE从 10g 开始就有了属于很基础的能力但同族函数之间版本差别不小REGEXP_COUNT是 11g 才加的REGEXP_SUBSTR的第六个参数指定返回第几个子表达式也是 11gR2 之后才支持的。如果你的库还是 10g这些都要注意。另外较新版本在正则引擎的实现上有过优化同样的模式在老版本上可能明显更慢这也是迁移评估时容易被忽略的一项。最后说一个我个人最看重的习惯把正则写成可测试的资产而不是散落在各处的字符串。我的做法是把常用的几个模式手机号、邮箱、日期、编号定义成一个 PL/SQL 包里的常量每个模式配套一组正例和反例的测试数据改之前先跑测试。这套东西看着麻烦但真到了需要改一次手机号规则的时候它能帮你省下整整一个下午的排查时间。踩过几次坑之后我越来越确信正则的难点从来不在语法而在于你有没有一套能快速确认它到底对不对的办法。