用过ODPS SQL做数据清洗的人，基本都绕不开正则表达式这个坎。特别是从MySQL、SQL Server转过来的同学，最容易在ODPS里被正则的写法坑到——不是匹配不到数据，就是莫名其妙报错，再就是跑出来的结果跟本地测的完全不一样。我自己刚接触ODPS那会儿，也在这个上面浪费了大把时间。
这篇东西我不打算写成一本正经的官方文档，就按实际开发里最常碰到的场景，把ODPS SQL里正则这几个函数的用法、坑点、性能问题，以及能直接抄的案例都整理出来。不管你是刚接手数仓的新手，还是被正则调参搞到头疼的老兵，读完应该都能少走点弯路。
1. ODPS里正则函数全家桶：每次匹配到底该用谁
ODPS SQL内置的正则相关函数不算多，但每个的定位和适用场景差异挺大的。很多人的误区是一上来就抱着regexp_extract不放，其实有些场景用别的函数会更省事。
1.1 regexp_extract：提取子串的万金油
regexp_extract是日常用得最多的一个，函数签名长这样：
string regexp_extract(string source, string pattern[, bigint groupid])它做的事情就是：从source里匹配pattern，然后取出某个分组的内容返回。第三个参数groupid控制取哪个分组，这个参数很多初学者容易搞混。需要注意，groupid传0表示返回整个匹配到的内容，传1、2、3才分别对应第1、第2、第3个括号里的分组。
-- 提取手机号码中间四位 select regexp_extract('13812345678', '(\\d{3})(\\d{4})(\\d{4})', 2); -- 返回：1234还有一点我一直提醒组里的人：groupid超出实际分组数时，函数返回的是空字符串，不是报错，也不是NULL。排查数据的时候要注意这个细节，不然你以为是数据问题，其实是参数传错了。
1.2 regexp_replace：替换比你想的更灵活
regexp_replace的签名是：
string regexp_replace(string source, string pattern, string replace_string[, bigint occurrence])前三个参数好理解，就是匹配、替换。第四个参数occurrence是个很实用的参数，表示只替换第几次匹配到的内容。默认传0表示替换所有匹配项；如果传的是正整数n，就只替换第n次匹配。
-- 把手机号中间四位替换成**** select regexp_replace('13812345678', '(\\d{3})\\d{4}(\\d{4})', '\\1****\\2'); -- 返回：138****5678这个\\1的用法是个重点。在替换串里，\\1、\\2这类写法可以引用前面正则里捕获的分组内容。很多人不知道替换串里也能用反向引用，结果写复杂替换逻辑时只能嵌套好几层函数，又慢又难维护。
1.3 regexp_instr：搞定“找位置”的需求
regexp_instr返回匹配内容在字符串中的位置，函数签名：
bigint regexp_instr(string source, string pattern[, bigint start_position[, bigint Nth_match]])start_position表示从第几个字符开始找，Nth_match表示找第几次匹配。默认值都是1。这个函数适合做前置判断或截取前的定位。
-- 找到第一个数字出现的位置 select regexp_instr('abc123def', '\\d'); -- 返回：4不过坦白讲，这个函数在ODPS里的使用频率远不如前两个高，大部分“找位置”的场景用instr（普通字符串定位函数）配合substr也能完成。只有位置本身就不固定、必须靠正则特征来定位的时候，才建议上regexp_instr。
1.4 配套函数补充：regexp_count 和 split_part
除了上面两个主力函数，还有两个经常一起用的。一个是regexp_count，直接统计匹配次数：
bigint regexp_count(string source, string pattern)另一个是split_part，按正则拆分字符串后取某一段：
string split_part(string source, string separator, bigint start[, bigint end])这两个在特定场景里能省不少事。比如判断某个字段里出现了几个数字、按多个分隔符切分字符串，都比自己写一堆instr+substr组合要干净得多。
2. ODPS正则和别的数据库不一样的坑：转义和字符匹配
这节必须单独拿出来讲。ODPS底层走的是Java的正则引擎，跟MySQL、SQL Server这类传统数据库有本质区别。JDK的正则语法更接近Perl风格，支持\d、\w、(?i)这些写法，但与此同时，ODPS SQL在自己的字符串解析层再加了一层转义。两层转义叠加，简直是新手重灾区。
2.1 反斜杠为什么要写两遍甚至四遍
在ODPS SQL的字符串字面量里，反斜杠本身是转义符。你想在正则里表达一个\d，如果只写'\d'，ODPS的SQL解析器会尝试去转义字母d，大概率报错或者丢字符。正确姿势是写'\\d'，这样SQL层把\\解析成一个反斜杠，剩余的d原样保留，最终传给正则引擎的才是\d。
来几个对照就清楚了：
-- 错误写法，会报错或匹配不到 select regexp_extract('abc123', '\d+', 0); -- 正确写法 select regexp_extract('abc123', '\\d+', 0);更极端的情况是匹配反斜杠本身。正则引擎里要匹配一个反斜杠，得写成\\，而SQL字符串里每个反斜杠又得写成\\，所以最终你在SQL里看到的是四个反斜杠'\\\\'。这个我当年第一次写的时候也愣了一下，后来总结成一个土办法：先想清楚正则引擎需要什么，再对着把每个反斜杠二倍化。
2.2 圆点和方括号在ODPS里的表现
.是正则里的万能匹配符，匹配除了换行以外的任意字符。很多从SQL Server转过来的同学会下意识以为.就是字面意义上的点号，结果匹配出来的结果五花八门。
-- 想匹配“19.99”里的点号，这样写会匹配任意字符 select regexp_extract('19.99', '19.99', 0); -- 返回：19.99 但也会错误匹配 19x99 这类脏数据 -- 正确写法：转义点号 select regexp_extract('19.99', '19\\.99', 0); -- 注意 SQL 里写的是 两个反斜杠再加点号方括号[...]用来定义字符集，这个跟其他数据库一致。[0-9]等价于\\d，[a-zA-Z0-9_]等价于\\w。ODPS里中文匹配要特别注意，普通[\\u4e00-\\u9fa5]写起来麻烦，后面第五章我会给一个实测好用的中文匹配方案。
2.3 常见转义对照速查表
把ODPS SQL里正则最常见的转义写法整理成一个表，建议直接收藏：
| 想匹配的内容 | 正则引擎写法 | ODPS SQL里的写法 |
|---|---|---|
| 数字 | \d | '\\d' |
| 非数字 | \D | '\\D' |
| 字母数字下划线 | \w | '\\w' |
| 空白字符 | \s | '\\s' |
| 点号（字面） | \. | '\\.' |
| 反斜杠（字面） | \\ | '\\\\' |
| 竖线（或） | | | '\|' |
| 左括号（字面） | \( | '\\(' |
每次写正则老出错的，可以把表贴在工位上。我后来养成的习惯是，写完正则先在测试SQL里跑一个简单的select验证，再丢到正式任务里，别直接改生产逻辑。
3. 正则函数和SQL怎么搭配，才是实战的最优解
单个函数会用了只是第一步。实际数仓开发里，正则很少孤零零出现，基本都是嵌在case when、where、lateral view里配合使用。怎么组合才高效、可读，这节讲几个高频模式。
3.1 用case when做正则多分支判断
ETL里最常见的场景是：一个字段有多种格式，每种格式要提取不同的东西，提取不到就返回默认值。这时候case when配合regexp_instr或者regexp_count做分支，比一堆嵌套的if清晰得多。
select case when regexp_instr(remark, '^订单') = 1 then '订单类型' when regexp_instr(remark, '^售后') = 1 then '售后类型' when regexp_instr(remark, '^物流') = 1 then '物流类型' else '其他' end as remark_type from dwd_order_remark_di;regexp_instr返回1就说明从字符串开头匹配上了，这个判断比regexp_like类的函数更直接。ODPS没有专门的regexp_like，习惯用instr来判断是否匹配，逻辑上完全等价。
3.2 正则提取多字段：一次扫描拿多个指标
从同一个字符串里提取多个部分，是正则使用的高频场景。ODPS里regexp_extract每次调用都会对字符串做一次正则扫描，所以能一次提取多个字段就别写多个regexp_extract。
做法是用一个正则模式，把要提取的内容都放到分组里，然后用不同的groupid去取：
select regexp_extract(log_line, 'user=(\\w+)&age=(\\d+)&city=(\\w+)', 1) as user_name, regexp_extract(log_line, 'user=(\\w+)&age=(\\d+)&city=(\\w+)', 2) as user_age, regexp_extract(log_line, 'user=(\\w+)&age=(\\d+)&city=(\\w+)', 3) as user_city from access_log;这三个regexp_extract用的是同一个pattern，ODPS的引擎一般能复用已编译的正则对象，实际开销没有想象中翻三倍那么大。但如果pattern本身很复杂、日志行又很长，建议还是拆成几步处理，别一口气把正则在SQL里写到又臭又长。
3.3 lateral view配合explode：处理一对多正则匹配
有时候正则一次能匹配出多个结果，比如字符串里内嵌了多个JSON片段，每个片段都要单独解析。这时候regexp_count统计个数、split_part配合lateral view展开，是业界比较通用的做法。
select t.id, split_part(t.json_part, ',', 1) as first_key, split_part(t.json_part, ',', 2) as second_key from ( select id, regexp_replace(json_str, '\\}', '}@@@') as processed_json from source_table ) t lateral view explode(split(t.processed_json, '@@@')) tmp as json_part;当然这个例子是简化版，真实场景里往往还需要JSON解析函数二次处理。但这个思路值得借鉴：先用正则把文本归一化，再用split+explode做展开，比直接在SQL里写循环逻辑（ODPS SQL本身也不支持循环）要高效得多。
4. 正则性能优化：为什么你的任务跑得比别人慢
正则表达式是出了名的性能杀手。ODPS是分布式计算引擎，一个正则的优劣会放大到每个MapTask上，一个任务几亿条数据，每条都做一次复杂的正则回溯，计算量直接翻好多倍。我排查过不少跑得异常慢的SQL，最后定位下来，正则写得烂的占比相当高。
4.1 贪婪匹配导致的回溯爆炸
正则默认是贪婪的，.*会尽可能多地匹配字符，然后一步一步往回退，这个往回退的过程就是“回溯”。遇到复杂嵌套和长文本时，回溯次数可能指数级增长。典型的问题正则长这样：
-- 错误示例：用.*去匹配中间内容，很容易触发大量回溯 select regexp_extract(log_line, 'start.*end', 0) from log_table;如果log_line非常长，.*会先吞掉整行，然后倒退找end，找不到再继续倒，性能极差。改成非贪婪写法.*?后，引擎会尽量少匹配，找到第一个end就停，整体效率好不少：
-- 优化示例：非贪婪匹配 select regexp_extract(log_line, 'start(.*?)end', 1) from log_table;4.2 能用普通字符串函数就别用正则
正则不是万能的。很多简单的提取，用substr、instr、split_part就够了，执行效率远高于正则。我的习惯是：先尝试用普通字符串函数解决，实在拿不下来再上正则。
一个典型案例是提取固定分隔符的字段，比如a_b_c_d结构，用split_part直接按_切分，完全不需要正则。还有些人喜欢用regexp_replace做字符替换，但如果是把全角逗号替换成半角，用translate或者replace就够了。
4.3 把长文本先截断再正则匹配
如果正则要处理的字段特别长，比如一整个HTML页面存进了字段里，而你的目标只是提取<title>标签里的内容。这时候直接在原始长文本上跑正则，效率很低。可以先instr定位到<title>的位置，再substr截一小段出来，最后在短的子串上跑正则。这个优化思路在很多慢SQL优化案例里都管用。
select regexp_extract( substr(content, instr(content, '<title>'), 200), '<title>(.*?)</title>', 1 ) as page_title from web_content_table;先缩小匹配窗口，让正则面对的数据量从几万字符降到几百字符，执行效率的提升通常是数量级的。
4.4 正则函数的几点性能备忘录
regexp_count、regexp_instr、regexp_extract这些函数本质上都会做正则编译和匹配，能少用就少用。- 正则pattern尽量写在SQL里常量位置，不要从字段里动态拼接pattern。动态pattern导致每行数据都要重新编译正则，性能会断崖式下跌。
- 过滤数据时，能用
where instr(col, '关键词') > 0的地方，不要写成where regexp_extract(col, '关键词', 0) is not null。 - ODPS执行引擎对复杂正则的pattern cache有限制，同一个SQL里大量不同的正则模式会导致缓存频繁失效，也是一笔隐形的开销。
5. 实战：正则清洗/提取案例集合
讲了这么多理论，最后拿几个真实的需求来收尾。这些都是我实际在ODPS开发中处理过的数据场景，直接复制改参数基本能跑。
5.1 手机号脱敏与身份证信息提取
-- 手机号脱敏 select regexp_replace('13812345678', '(\\d{3})\\d{4}(\\d{4})', '\\1****\\2'); -- 身份证提取出生日期 select regexp_extract('110101199003077654', '(\\d{6})(\\d{4})(\\d{2})(\\d{2})(\\d{3})(\\d|X)', 2 || '-' || 3 || '-' || 4);第二条的||拼接写法在有些版本里可能要调整，更稳妥的做法是分两次提取再拼接，避免把pattern写得太复杂。
5.2 URL参数解析
日志表里经常存了整串URL，要提取其中某个参数值：
select regexp_extract(url, '[?&]token=([^&]+)', 1) as token, regexp_extract(url, '[?&]from=([^&]+)', 1) as from_source from access_log where url like '%token=%';[^&]+表示匹配到下一个&为止，这个写法在解析URL和query string时非常实用，建议记下来。
5.3 中文匹配与特殊字符清洗
ODPS正则引擎支持Unicode，但匹配中文建议直接用[\\u4e00-\\u9fa5]：
-- 提取所有中文字符 select regexp_replace('hello世界123', '[^\\u4e00-\\u9fa5]', ''); -- 返回：世界 -- 判断字符串是否全中文 select if(regexp_count(name, '[\\u4e00-\\u9fa5]') = length(name), 1, 0) as is_all_chinese from user_table;注意第二个写法在name包含全角字符或生僻字时可能不准，生产环境建议先抽样验证。
5.4 JSON字段里的多层提取
ODPS有内置的get_json_object函数，但遇到不规则JSON还是会用到正则兜底。比如从key:value;key:value结构中取某个值：
select regexp_extract( info_str, 'city\\s*[:=]\\s*([^;]+)', 1 ) as city_name from user_info_table;5.5 多分隔符拆分
某些脏数据用混合分隔符，比如空格、逗号、全角逗号都可能出现。用正则统一清洗后再拆分：
select split_part(regexp_replace(raw_col, '[,\\s，]+', ','), ',', 1) as part1, split_part(regexp_replace(raw_col, '[,\\s，]+', ','), ',', 2) as part2 from raw_table;6. 个人踩坑经验总结
写ODPS正则这几年，我踩过最深的一个坑就是“本地测试没问题，上了集群就变了”。后来才明白，ODPS的正则引擎和本地用Python/Java测的引擎在个别边界行为上是有差异的。比如\d在某些版本的ODPS引擎里不只匹配0-9，还可能匹配某些Unicode数字字符。涉及到敏感数据匹配时，宁可把字符集写死成[0-9]，也不要用\d赌引擎行为。
另一个经验是正则分组越少越好。每次看到一串超过20个字符、括号嵌套好几层的正则，我都建议拆开来写。可读性差不说，后续接手维护的人改一处就可能改崩全局。我现在的习惯是：先写注释说明要匹配的原始样本，再写正则，最后写验证SQL。这样三个月后回来看这段代码，还能快速想起来当初为什么这么写。
最后一点，正则再怎么厉害，也只是数据处理的最后一公里。上游如果能在采集阶段就把字段规整好，比在下游用正则硬解要省事得多。给上游提需求、推动规范化，往往比无限提升自己的正则水平更有效。不过现实嘛，总有各种历史原因弄出脏数据，那就打开ODPS编辑器，老老实实写正则吧。
