Oracle数据库从10g版本开始引入了正则表达式支持，提供了一套以regexp开头的SQL函数，包括regexp_like、regexp_substr、regexp_replace、regexp_instr和regexp_count等。这些函数弥补了传统LIKE运算符只能做简单通配符匹配的不足，能够处理模式校验、内容提取、批量替换等复杂场景。本文重点讲解其中最常用的三个函数：regexp_like、regexp_substr和regexp_replace，并结合实际案例说明它们的用法细节。
一、Oracle正则表达式的基础语法要点
在学习具体函数之前，先要了解Oracle正则表达式的书写规则。Oracle采用的是POSIX扩展正则语法，与常见的PCRE语法存在一些差异，例如不支持\d表示数字，需要用[0-9]或[[:digit:]]代替。常用的元字符包括：点号.匹配任意单个字符，星号*表示前一个元素出现零次或多次，加号+表示一次或多次，问号?表示零次或一次，竖线|表示多选一结构，圆括号用于分组，方括号用于字符集合。
Oracle还提供了一些特殊的匹配选项，称为匹配参数（match_parameter），可以附加在函数最后一个参数位置。常用的有：i表示忽略大小写，c表示区分大小写，n表示允许点号匹配换行符，m表示多行模式。如果省略该参数，则由NLS排序参数决定大小写行为。
另外要注意，Oracle正则中出现次数的写法是{m,n}，例如{3}表示恰好出现3次，{1,4}表示出现1到4次。转义字符使用反斜杠，比如要匹配字面意义上的点号，需要写成\.，这在匹配IP地址或文件名时非常关键，很多初学者因为漏掉转义导致匹配结果异常。
二、regexp_like函数：条件匹配与数据校验
regexp_like是最基础的正则函数，返回布尔类型的结果，通常用在WHERE条件或CHECK约束中。它的语法是regexp_like(source_string, pattern[, match_parameter])，第一个参数是待检查的字符串，第二个参数是正则模式。
下面通过几个典型例子说明它的用法。第一个例子是查询姓氏以张或李开头的员工：
SELECT * FROM employees WHERE regexp_like(emp_name, '^张|^李');
第二个例子是校验手机号格式，中国大陆手机号为11位数字且以1开头，第三位通常是3到9之间的数字：
SELECT '13812345678' AS phone FROM dual
WHERE regexp_like('13812345678', '^1[3-9][0-9]{9}$');
-- 在CHECK约束中使用正则校验
ALTER TABLE members ADD CONSTRAINT chk_phone
CHECK (regexp_like(phone, '^1[3-9][0-9]{9}$'));
第三个例子是校验邮箱格式，需要匹配用户名、@符号和域名部分：
SELECT email FROM user_info
WHERE regexp_like(email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
regexp_like也支持忽略大小写匹配。例如查找包含oracle关键字（不区分大小写）的记录：
SELECT * FROM docs WHERE regexp_like(content, 'oracle', 'i');
三、regexp_substr函数：按模式提取字符串片段
regexp_substr用于从字符串中提取符合正则模式的子串，相当于增强了截取能力的substr函数。基本语法为regexp_substr(source_string, pattern[, position[, occurrence[, match_parameter[, subexpr]]]])，其中position指定起始搜索位置，occurrence指定匹配第几次出现的内容，subexpr指定返回模式中第几个分组。
一个常见需求是从一串混合文本中提取手机号。假设备注字段中夹杂着文字和号码，可以这样写：
SELECT regexp_substr(remark, '1[3-9][0-9]{9}') AS mobile
FROM orders
WHERE remark IS NOT NULL;
提取IP地址也是一个经典场景。由于点号是元字符，必须转义：
SELECT regexp_substr(log_text, '([0-9]{1,3}\.){3}[0-9]{1,3}') AS ip_addr
FROM sys_logs;
occurrence参数配合connect by可以实现拆分字符串的效果，例如把逗号分隔的标签拆成多行：
SELECT regexp_substr('Java,Python,Go,SQL', '[^,]+', 1, LEVEL) AS tag
FROM dual
CONNECT BY LEVEL <= regexp_count('Java,Python,Go,SQL', ',') + 1;
这段SQL的原理是：level从1递增，regexp_substr依次取出第1个、第2个、第3个不包含逗号的片段，而regexp_count负责统计逗号数量来确定总行数。这种写法在实际拆分业务数据时非常实用。
四、regexp_replace函数：模式替换与格式清洗
regexp_replace按照正则模式查找并替换字符串内容，语法为regexp_replace(source_string, pattern[, replace_string[, position[, occurrence[, match_parameter]]]])。当occurrence为0或缺省时，替换所有匹配项。
典型用法是数据脱敏，比如把手机号中间四位替换为星号：
SELECT regexp_replace('13812345678', '(1[3-9][0-9]) [0-9]{4} ([0-9]{4})', '\1****\2')
FROM dual;
-- 正确写法（无空格）
SELECT regexp_replace('13812345678', '(1[3-9][0-9])([0-9]{4})([0-9]{4})', '\1****\3') AS masked
FROM dual;
这里用圆括号捕获分组，替换串中的\1和\3分别引用第一组和第三组捕获的内容，从而保留号码头尾、隐藏中间部分。反向引用是regexp_replace最强大的特性之一，熟练使用可以完成很多精细的文本处理。
其他常见用法还包括：去除字符串中的所有非数字字符、规范日期分隔符等：
-- 只保留数字
SELECT regexp_replace('订单号: A2023-0091-X', '[^0-9]', '') FROM dual;
-- 把各种日期分隔符统一为短横线
SELECT regexp_replace('2024/06/15', '[/.]', '-') FROM dual;
-- 压缩连续空格为单个空格
SELECT regexp_replace('hello world test', ' +', ' ') FROM dual;
五、使用注意事项与性能建议
正则函数虽然灵活，但代价是性能开销明显高于普通函数。正则表达式在执行时需要编译模式并进行回溯匹配，对大表做全列扫描时，正则条件的耗时可能是LIKE的数倍。因此能简单匹配的场景尽量用LIKE，只有模式复杂时才使用正则。
其次要避免正则书写错误导致的意外匹配。比如没有使用^和$锚定时，regexp_like做的是包含匹配而非完整匹配，校验手机号时如果不加锚点，一个21位的字符串中间包含合法手机号也会通过校验。此外，Oracle正则不支持负向前瞻断言，一些在其他语言中可行的写法在Oracle中会直接报错，需要换用其他思路实现。
最后建议在正式使用前，先在dual表上验证正则表达式的正确性，确认匹配结果符合预期后再应用到业务SQL中。对于频繁执行的正则逻辑，可以考虑将结果物化到字段中，减少运行时的重复计算，这在数据仓库类的查询优化中尤为有效。
regexp_likeregexp_substrregexp_replaceOracle正则表达式修改时间：2026-09-15 14:00:37