前阵子接了一个客户数据清洗的活儿几百条手机号里什么格式都有86 138-1234-5678、138 1234 5678、13812345678甚至还有汉字备注混在里面的。当时如果一个个用REPLACE去套写出来的 SQL 能绕地球一圈。但换成REGEXP_REPLACE一条语句就把整个清洗逻辑讲清楚了。这篇文章要把REGEXP_REPLACE的使用方法一次性聊透函数到底怎么工作、日常清洗和脱敏怎么写、不同数据库有哪些差异以及我实际使用中踩过的坑。适合正在做数据清洗、报表加工、数据脱敏的同学也适合刚接触正则表达式的 SQL 新手。不管你是 Oracle、MySQL 还是 PostgreSQL 用户这篇都能直接拿过去用。1. 为什么SQL文本清洗就该用正则替换而不该用REPLACE硬拼1.1 REPLACE的短板只能精确匹配固定字符串很多初学者第一次面对脏数据时第一反应就是REPLACE。比如把手机号里的-去掉写REPLACE(phone, -, )把空格去掉就再套一层REPLACE(REPLACE(phone, -, ), , )。这种写法在模式固定、替换项极少的时候没问题但一旦遇到下面几种情况就彻底失控要清理的符号不确定今天发现数据里有-明天又冒出_后天出现全角括号REPLACE只能一个一个罗列SQL 越来越长光看嵌套就得拆半天。只能处理一模一样的字符串哪怕只是想删掉所有数字、保留汉字REPLACE也做不到因为它没有按类别匹配的能力。多个条件叠加时逻辑混乱多个REPLACE嵌套看起来是解决了但一旦调换顺序结果就可能改变后续维护的人根本不知道哪个符号被先处理了。REGEXP_REPLACE解决的就是这个问题它不关心你要删的具体是什么字符而是关心你要删的字符长什么样。1.2 从精确替换到按特征替换的思维转变打个比方REPLACE就像你去图书馆找一本你记得书名的书而REGEXP_REPLACE是你说我要把书架上所有红色封面且厚度超过两厘米的书都拿出来换个位置。前者要求你精确描述目标后者要求你描述目标特征。正则替换真正擅长的是这些场景按字符类型清洗只保留数字、只保留汉字、只保留字母和空格。按格式片段清洗去掉 HTML 标签、去掉 JSON 里的转义符、去掉 URL 参数。按出现位置替换从第几位开始处理、只替换第几次出现的内容。内容重排把20240101这种格式转成2024-01-01把姓名从姓名变成名姓。内容脱敏把手机号中间四位替换成****把邮箱用户名只保留第一个字符。1.3 先看清你手上的数据库支持不支持这一步非常重要很多人在写完 SQL 报错后才想起来查版本。从支持情况来看各数据库差异不小数据库函数名原生支持情况备注OracleREGEXP_REPLACE原生支持10g 及以后版本语法最完整参数最多MySQLREGEXP_REPLACE8.0 及以后版本原生支持5.7 及更早版本没有这是高频报错点MariaDBREGEXP_REPLACE原生支持10.0.5 以后可用PostgreSQLregexp_replace原生支持函数名为小写第四个参数传 flagsHive / Spark SQLREGEXP_REPLACE原生支持大数据场景常用SQL Server无对应原生函数不原生支持只能用 CLR 或字符串函数模拟后面专门说SQLiteREPLACE可用正则需扩展需加载扩展默认编译不含正则提示MySQL 5.7 用户如果直接写REGEXP_REPLACE数据库会报 FUNCTION 不存在。这种情况下要先升级到 8.0或者用REPLACE嵌套勉强顶着但复杂清洗基本无解。2. 语法拆解与执行逻辑REGEXP_REPLACE是怎么找和换的2.1 完整函数签名与五个关键参数以语法最完整的 Oracle 为例函数签名是REGEXP_REPLACE( source_string, -- 源字符串要处理的文本 pattern, -- 正则模式描述你要找什么 replacement, -- 替换成什么支持反向引用 \1 \2 position, -- 从源字符串的第几个字符开始查找默认 1 occurrence, -- 替换第几次匹配0 表示全部替换 match_param -- 匹配选项如大小写敏感、多行模式 )前三个参数是必须的后三个可省略。大多数人日常只用到前三个但后面这几个参数在一些特殊场景里能救命。MySQL 和 Oracle 的参数顺序基本一致REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])PostgreSQL 则把参数精简了regexp_replace(source, pattern, replacement [, flags ])这里有个非常关键的差异PostgreSQL 默认只替换第一个匹配项如果要替换全部必须在 flags 参数里加g。而 Oracle 和 MySQL 默认就是替换全部匹配项。最容易踩的一个例子在 PostgreSQL 里写regexp_replace(a1b2c3, [0-9], )结果是abc吗不是结果是ab2c3因为它只替换了第一个匹配的数字。必须写成SELECT regexp_replace(a1b2c3, [0-9], , g); -- 结果abc2.2 位置和次数参数从第几位开始、替换第几次position参数决定了从源字符串的哪个字符位置开始查找。比如-- Oracle SELECT REGEXP_REPLACE(2024-01-15, [0-9], #, 6) FROM dual; -- 从第6个字符开始找数字前5个字符不动 -- 第6位之后的所有数字都被替换2024-##-##occurrence参数决定替换第几次匹配。Oracle 和 MySQL 里0表示替换所有匹配1表示只替换第一个2表示只替换第二个。这个参数在实际中非常有用比如你只想把电话号码区号里的 0 去掉而不影响后面号码里的 0-- Oracle只替换第一次出现的数字 0 SELECT REGEXP_REPLACE(010-12345678, 0, , 1, 1) FROM dual; -- 结果10-12345678PostgreSQL 没有 occurrence 参数要表达只替换第 N 次匹配会比较绕通常要靠模式本身去约束。2.3 子表达式引用replacement中的\1、\2这是REGEXP_REPLACE最强大的能力正则可以记住匹配到的某几个片段然后在替换文本里重新排列。正则表达式里用括号()包起来的部分叫子表达式引擎会给它们编号第一个左括号对应\1第二个对应\2以此类推。替换字符串里写\1就代表把第一个括号匹配到的内容原样放回这里。看一个最简单例子把日期格式从20240115改成2024-01-15-- Oracle SELECT REGEXP_REPLACE( 20240115, (\d{4})(\d{2})(\d{2}), \1-\2-\3 ) FROM dual; -- 结果2024-01-15这里\d{4}表示匹配四个数字括号包住后分别成为\1、\2、\3替换字符串里把它们重新拼装成带横杠的格式。整个过程就是在做拆解-重排。2.4 匹配选项大小写敏感、多行模式的细节match_param参数是个字符串里面可以组合多个标志位常见的有c大小写敏感匹配默认。i大小写不敏感匹配。n让.匹配换行符。默认情况下.不匹配换行。m把字符串按多行处理让^和$匹配每一行的行首和行尾而不仅是整个字符串的开头和结尾。x忽略模式里的空白字符Oracle 支持。比如要把SQL无论大小写都替换成结构化查询语言-- Oracle SELECT REGEXP_REPLACE(I love sql and Sql, sql, 结构化查询语言, 1, 0, i) FROM dual; -- 结果I love 结构化查询语言 and 结构化查询语言如果去掉i则只有小写sql被替换大写Sql不会动。这个选项在清洗用户输入、统一术语时太常用了。3. 数据清洗实战把脏数据变成标准数据的五组典型写法3.1 只保留数字电话号码和证件号清洗我刚接手的手机号清洗需求核心就一句话把字符串里所有的非数字字符统统去掉。正则表达式是[^0-9]含义是只要不是数字就替换成空串。-- MySQL 8.0 SELECT phone, REGEXP_REPLACE(phone, [^0-9], ) AS clean_phone FROM customer_tmp;原始值86 138-1234-5678会变成8613812345678。注意如果不想要86这个国际区号还得先用REPLACE去掉86前缀或者用正则精确匹配1[3-9][0-9]{9}这种手机号模式再提取。这提醒我们清洗规则一定要想清楚要什么而不是只想着删什么。3.2 统一分隔符让日期、金额、编码格式归一化业务系统里日期格式经常五花八门2024/01/15、2024.01.15、20240115。要统一成2024-01-15无非是把/、.替换成--- Oracle SELECT REGEXP_REPLACE(2024/01/15, [/.], -) FROM dual; -- 结果2024-01-15 -- MySQL 8.0 写法相同方括号[/.]表示匹配/或.中的任意一个字符。注意里面的.在字符类里是普通字符不需要转义。这种批量化处理是REPLACE很难优雅实现的。3.3 移除HTML标签与JSON转义残留处理网页抓取的数据时字段里经常混着p、span之类的标签。要去掉所有 HTML 标签正则模式是[^]-- MySQL 8.0 SELECT REGEXP_REPLACE( p姓名张三/pspan电话13812345678/span, [^], ) AS clean_text; -- 结果姓名张三电话13812345678这里为什么不写成\w因为标签里往往还有属性比如span classred\w匹配不完。[^]的含义是匹配一个左尖括号后面跟着至少一个非右尖括号的字符最后是右尖括号对带属性、带空格的标签都有效。JSON 字段里的转义残留也是类似的思路比如把{\name\:\张三\}里的反斜杠去掉-- MySQL 8.0 SELECT REGEXP_REPLACE({\name\:\张三\}, \\, ); -- 结果{name:张三}3.4 清理不可见字符换行、制表符、回车从 Excel 或外部文件导入的数据经常在字段末尾带着\r\n。这些字符在查询结果里肉眼看不见但LENGTH偏大、导出文件对不上账、界面展示出现诡异换行问题排查半天才定位到是它。用正则把这类空白字符全部替换掉-- MySQL 8.0 -- 注意MySQL 字符串里 \\r 表示回车、\\n 表示换行 SELECT REGEXP_REPLACE( 第一行\r\n第二行\t结束, [\\r\\n\\t], ) AS clean_text;如果想把连续多个空白字符压缩成单个空格用量词SELECT REGEXP_REPLACE(a b\t\tc, [\\s], );\s在大部分数据库的正则引擎里代表空白字符类但为了最大兼容性[\\r\\n\\t ]这种写法更稳妥。3.5 批量替换多种脏词用竖线合并模式有时候要在一堆数据里把多种禁用词或脏词统一替换成***。用REPLACE得嵌套 N 层但是正则里用竖线|表示或一条正则就能覆盖-- MySQL 8.0 SELECT REGEXP_REPLACE( 你好测试公司客服电话010-123456。, 测试公司|客服电话[0-9-], *** ) AS clean_text; -- 结果你好******。注意竖线分支的顺序正则引擎会从左到右尝试分支如果两个分支都能匹配优先用靠左的那个。实际业务里如果发现替换结果不符合预期先检查分支顺序。4. 数据脱敏与信息重排子表达式的进阶玩法4.1 手机号、邮箱脱敏只保留头尾数据导出给测试环境或第三方时手机号一般要打码。手机号是 11 位数字要保留前 3 位和后 4 位中间 4 位换成****-- Oracle SELECT REGEXP_REPLACE( 13812345678, (\d{3})\d{4}(\d{4}), \1****\2 ) AS masked_phone FROM dual; -- 结果138****5678MySQL 8.0 里写法和 Oracle 基本相同唯一要小心的是替换字符串里的反斜杠引用。MySQL 的字符串里反斜杠是转义符所以要写成\\1****\\2-- MySQL 8.0 SELECT REGEXP_REPLACE(13812345678, (\\d{3})\\d{4}(\\d{4}), \\1****\\2); -- 结果138****5678邮箱脱敏类似保留第一个字符和后面的域名中间全部变***-- MySQL 8.0 SELECT REGEXP_REPLACE( zhangsanexample.com, ^(.).*, \\1*** ) AS masked_email; -- 结果z***example.com4.2 身份证脱敏与科学计数法问题很多热搜里提到Oracle 导出身份证变成科学计数法这其实是Excel 展示层把超过 15 位的数字自动转成了科学计数法不是数据库里数据出了问题。但我们在 SQL 层做脱敏时能顺手把这个问题也治了。假设身份证号在库里以字符串存储110101199001011234脱敏规则是保留前 6 位和后 4 位-- Oracle SELECT REGEXP_REPLACE( 110101199001011234, (\d{6})\d{8}(\d{4}), \1********\2 ) AS masked_id FROM dual; -- 结果110101********1234如果身份证号因为历史原因被存成了 NUMBER 类型导出时才会出现科学计数法。正确做法是导出前先转成字符串并固定宽度-- Oracle用 TO_CHAR 强制转成 18 位字符串 SELECT TO_CHAR(id_card, FM999999999999999999) AS id_card_str FROM user_table;然后再套脱敏正则。这是两个问题别混在一起。4.3 日期与业务编码重排子表达式引用最实用的场景就是重排。比如序列号规则调整要从A-12345-2024改成2024-12345-A-- Oracle SELECT REGEXP_REPLACE( A-12345-2024, ^([A-Z])-([0-9]{5})-([0-9]{4})$, \3-\2-\1 ) AS refactored_code FROM dual; -- 结果2024-12345-A注意这里用了^和$锚定整个字符串避免匹配到子串。这类重排逻辑如果用字符串拼接函数SUBSTRINSTR也能做但要写三四层嵌套可读性差很多。4.4 先验证再落地一条SELECT解决的事别直接UPDATE我在实际项目里有一条铁律所有涉及UPDATE的正则替换必须先写一条等价的SELECT验证结果确认后再动手改数据。-- 第一步先查询看 before 和 after 是否正确 SELECT phone, REGEXP_REPLACE(phone, [^0-9], ) AS clean_phone FROM customer WHERE phone REGEXP_REPLACE(phone, [^0-9], ) LIMIT 100; -- 第二步确认无误再更新 UPDATE customer SET phone REGEXP_REPLACE(phone, [^0-9], ) WHERE phone REGEXP_REPLACE(phone, [^0-9], );加上WHERE条件只处理有变化的行既能减少无效更新又能避免全表锁时间的浪费。这一步在千万级大表上尤其重要。5. 我踩过的几个坑与完整排查链路5.1 排错实录一函数不存在问题出在版本而不是写法有次在客户的 MySQL 5.7 实例上执行REGEXP_REPLACE报错FUNCTION database.REGEXP_REPLACE does not exist。我当时第一反应是函数名写错了检查半天没问题最后SELECT VERSION();一查5.7。排查链路是这样的先确认数据库类型和版本SELECT VERSION();。确认函数在当前版本是否可用物理上没这个函数怎么写都没用。MySQL 5.7 的临时方案如果是 8.0 之前只能用多层REPLACE或考虑升级如果只是需要提取数字这种简单需求可以用REGEXP_REPLACE前的兼容写法或者把数据抽出来用 Python/Java 清洗后再导回。这个坑本身不深但特别容易误判成语法问题。记住一句话先看版本再查语法。5.2 排错实录二贪婪匹配把整段内容吞了在 PostgreSQL 里清洗 HTML 字段时我一开始写的是regexp_replace(html, .*, , g)结果发现一大段正常文本也被删了。原因就是正则里的.*是贪婪匹配它会尽可能多地把字符吞进去。对单行文本来说.*从第一个会一直匹配到最后一个中间所有内容都被当作标签删掉了。排查过程是在小样本上反复试出来的。正确写法是用[^]让匹配停在第一个右尖括号处。[^]表示匹配一个或多个非 的字符天然不会跨标签。这个坑也提醒我们.*要用但用之前必须想清楚贪婪问题。若想匹配尽量少的内容标准做法是用非贪婪写法.*?但很多数据库的正则引擎对非贪婪支持不一定完整所以用[^]这种反向字符类更可靠。5.3 排错实录三替换字符串里的反斜杠被吃掉了在 MySQL 8.0 里做脱敏我最初按照 Oracle 的习惯写REGEXP_REPLACE(13812345678, (\\d{3})\\d{4}(\\d{4}), \1****\2)结果输出的不是138****5678而是13812345678或是带奇怪字符的内容。原因在于 MySQL 的字符串字面量中反斜杠本身是转义字符。\1在 MySQL 字符串里会被解释成控制字符而不是正则替换用的反向引用。正确写法是REGEXP_REPLACE(13812345678, (\\d{3})\\d{4}(\\d{4}), \\1****\\2)把替换字符串里的\1写成\\1。Oracle 默认字符串里反斜杠不转义所以用\1就行PostgreSQL 的standard_conforming_strings默认开启普通字符串里反斜杠也是普通字符写\1即可。这个差异非常隐蔽跨库迁移时一定要逐条检查替换字符串。5.4 排错实录四不匹配就返回原串NULL判断反而出错REGEXP_REPLACE在找不到匹配时返回的是原始字符串不是 NULL更不是空串。这意味着你不能用REGEXP_REPLACE(col, pattern, ) IS NULL来判断是否包含匹配内容。一次统计清洗效果时我写了这样的判断结果统计数据明显偏少-- 错误写法想统计被替换过的行数 SELECT COUNT(*) FROM customer WHERE REGEXP_REPLACE(phone, [^0-9], ) IS NULL;实际上根本没有行会返回 NULL正确做法是用REGEXP_LIKE先判断SELECT COUNT(*) FROM customer WHERE REGEXP_LIKE(phone, [^0-9]);同理如果想把空串也处理掉NULLIF是个好搭配SELECT NULLIF(REGEXP_REPLACE(phone, [^0-9], ), ) AS clean_phone FROM customer;5.5 SQL Server没有原生REGEXP_REPLACE怎么办这是最容易让 SQL Server 用户崩溃的一点。T-SQL 至今没有内置通用正则替换函数SQL Server 2022 也没有。常用的替代路子有这么几条方案一多层 REPLACE。适合模式固定且数量有限的清洗场景。优点是简单缺点是嵌套深、难以维护。SELECT REPLACE(REPLACE(REPLACE(phone, -, ), , ), (, ) AS clean_phone FROM customer;方案二用 PATINDEX STUFF 写一个自定义的循环替换函数。适合简单模式比如去掉所有非数字字符。PATINDEX支持%[^0-9]%这种带字符集的模糊匹配可以找到第一个非法字符的位置再用STUFF删掉它循环直到没有匹配为止。CREATE FUNCTION dbo.RemoveNonDigits(input NVARCHAR(MAX)) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE pos INT; SET pos PATINDEX(%[^0-9]%, input); WHILE pos 0 BEGIN SET input STUFF(input, pos, 1, ); SET pos PATINDEX(%[^0-9]%, input); END RETURN input; END GO SELECT dbo.RemoveNonDigits(86 138-1234-5678); -- 结果8613812345678方案三用 CLR 自定义函数集成 .NET 正则。适合复杂正则需求性能在大量数据处理时更好但部署和维护成本高需要数据库管理员配合。如果你的 Oracle/MySQL 脚本需要迁移到 SQL Server务必提前评估字符串清洗逻辑因为它不是简单替换函数名就能解决的。6. 正则替换的函数族配合与性能优化思路6.1 和REGEXP_LIKE、REGEXP_SUBSTR、REGEXP_INSTR配合REGEXP_REPLACE只是正则函数家族的一员。实际数据清洗中四个函数经常配合使用REGEXP_LIKE判断字符串是否符合某个模式返回 TRUE/FALSE。通常用来过滤数据或作为WHERE条件筛选需要清洗的行。REGEXP_INSTR找到匹配模式的位置返回一个数字。相当于增强版INSTR。REGEXP_SUBSTR从字符串中提取匹配的部分返回一个字串。相当于增强版SUBSTR。REGEXP_REPLACE替换匹配的部分。比如你要从一段文本里提取所有手机号可以先判断再提取-- Oracle提取符合手机号规则的片段 SELECT REGEXP_SUBSTR( 联系人张三电话13812345678备用18512345678, 1[3-9][0-9]{9} ) AS first_phone FROM dual;如果想在清洗前先确认哪些行包含特殊符号用REGEXP_LIKE过滤比直接REGEXP_REPLACE再比较更高效。6.2 能用普通函数解决就别用正则正则表达式不是万能的它写起来爽但性能往往比不上普通字符串函数。原因很简单正则引擎需要对每个字符做状态机匹配开销比REPLACE、SUBSTR高一个量级。我的经验法则是替换固定字符用REPLACE比如-换成空串。替换一组指定字符用TRANSLATEOracle或TRANSLATEPostgreSQL比如把-、空格、(、)一次性替换成空串TRANSLATE比正则快很多。只有模式不固定、需要按类型或结构处理时才用REGEXP_REPLACE。-- OracleTRANSLATE 一次性去掉多个字符比正则快 SELECT TRANSLATE(138-1234(5678), 1-(), 1) FROM dual;注意TRANSLATE的语义是字符级映射用法和各数据库略有差异用前先看文档。6.3 让清洗查询跑得动生成列、函数索引与数据分批REGEXP_REPLACE直接在WHERE条件中使用时数据库很难走索引性能差是正常的。优化思路有几个一种是把清洗结果物化到表里。MySQL 5.7 之后支持生成列可以建一个虚拟列或存储列来保存清洗后的结果并给这个列加索引ALTER TABLE customer ADD COLUMN clean_phone VARCHAR(20) GENERATED ALWAYS AS (REGEXP_REPLACE(phone, [^0-9], )) STORED; CREATE INDEX idx_customer_clean_phone ON customer(clean_phone);这样后续查询直接WHERE clean_phone 13812345678不用每次全表扫描时都现场算一遍正则。Oracle 则可以用函数索引CREATE INDEX idx_customer_clean_phone ON customer(REGEXP_REPLACE(phone, [^0-9], ));另一个思路是控制更新粒度。大表更新时别一条UPDATE全表跑容易产生长时间锁和大量日志。按主键范围分批处理每批 1000 到 10000 行配合循环对线上环境影响小很多。最后说一个我自己的习惯现在写任何一条REGEXP_REPLACE我都会先开一个 SELECT 验证窗口把 before 和 after 并排打出来肉眼确认后才会落到UPDATE。正则表达式复杂度一上来人眼根本不靠谱。另外一个技巧是把常用清洗正则沉淀成一张配置表字段名、正则模式、替换文本、适用场景都记录下来后面接到新需求直接翻表抄能省掉大量试错时间。正则这个东西熟练之后很顺手但它在 SQL 里跑的是真实数据谨慎永远不过分。