DB / Oracle / SQL 开发

Oracle 正则表达式（Regular Expression）

2026.09.06

Oracle 正则表达式（Regular Expression）

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

Oracle 支持 POSIX 正则表达式[1]：

函数：

  • REGEXP_LIKE
  • REGEXP_REPLACE
  • REGEXP_SUBSTR
  • REGEXP_INSTR
  • REGEXP_COUNT

2. 正则语法

2.1 元字符

字符说明
.任意字符
*0 次或多次
+1 次或多次
?0 次或 1 次
{n}n 次
{n,}至少 n 次
{n,m}n 到 m 次
^行首
$行尾
[]字符集
|或
()分组
\转义

2.2 字符类

类说明
[:alpha:]字母
[:digit:]数字
[:alnum:]字母数字
[:space:]空白
[:upper:]大写
[:lower:]小写
[:punct:]标点

2.3 简写

简写说明
\d数字
\D非数字
\w单词字符
\W非单词字符
\s空白
\S非空白

3. REGEXP_LIKE

3.1 基本用法

-- 查询包含数字的员工
SELECT * FROM employees
WHERE REGEXP_LIKE(last_name, '[0-9]');

-- 查询以 A 开头的
SELECT * FROM employees
WHERE REGEXP_LIKE(last_name, '^A');

-- 查询以 n 结尾的
SELECT * FROM employees
WHERE REGEXP_LIKE(last_name, 'n$');

-- 邮箱
SELECT * FROM customers
WHERE REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

3.2 匹配模式

-- i: 不区分大小写
-- c: 区分大小写
-- n: 允许 . 匹配换行
-- m: 多行模式
-- x: 忽略空白

SELECT * FROM employees
WHERE REGEXP_LIKE(last_name, 'smith', 'i');

4. REGEXP_REPLACE

4.1 基本替换

-- 替换数字为 X
SELECT REGEXP_REPLACE(phone, '[0-9]', 'X') FROM customers;

-- 删除所有非数字
SELECT REGEXP_REPLACE(phone, '[^0-9]', '') FROM customers;

-- 隐藏邮箱用户名
SELECT REGEXP_REPLACE(email, '([^@]+)@', '***@') FROM customers;

4.2 回引

-- 交换姓和名
SELECT REGEXP_REPLACE('John Smith', '([A-Za-z]+) ([A-Za-z]+)', '\2 \1') FROM dual;
-- Smith John

-- 电话格式化
SELECT REGEXP_REPLACE('13812345678', '([0-9]{3})([0-9]{4})([0-9]{4})', '\1-\2-\3') FROM dual;
-- 138-1234-5678

5. REGEXP_SUBSTR

5.1 提取

-- 提取数字
SELECT REGEXP_SUBSTR('Order 12345 confirmed', '[0-9]+') FROM dual;
-- 12345

-- 提取邮箱
SELECT REGEXP_SUBSTR(text, '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}') 
FROM messages;

-- 提取第 n 个匹配
SELECT REGEXP_SUBSTR('a,b,c,d', '[^,]+', 1, 3) FROM dual;
-- c

5.2 子组

-- 提取子组
SELECT 
  REGEXP_SUBSTR('2026-07-21', '([0-9]+)-([0-9]+)-([0-9]+)', 1, 1, NULL, 1) AS year,
  REGEXP_SUBSTR('2026-07-21', '([0-9]+)-([0-9]+)-([0-9]+)', 1, 1, NULL, 2) AS month,
  REGEXP_SUBSTR('2026-07-21', '([0-9]+)-([0-9]+)-([0-9]+)', 1, 1, NULL, 3) AS day
FROM dual;
-- 2026, 07, 21

6. REGEXP_INSTR

6.1 位置

-- 查找位置
SELECT REGEXP_INSTR('Hello World', 'o') FROM dual;
-- 5

-- 查找第 n 个
SELECT REGEXP_INSTR('Hello World', 'o', 1, 2) FROM dual;
-- 8

6.2 返回选项

-- 0: 返回匹配开始位置
-- 1: 返回匹配结束位置

SELECT REGEXP_INSTR('Hello World', 'o', 1, 1, 0) FROM dual;  -- 5
SELECT REGEXP_INSTR('Hello World', 'o', 1, 1, 1) FROM dual;  -- 6

7. REGEXP_COUNT

-- 统计出现次数
SELECT REGEXP_COUNT('Hello World', 'o') FROM dual;
-- 2

SELECT REGEXP_COUNT('a,b,c,d', ',') FROM dual;
-- 3

8. 常用模式

8.1 邮箱

'^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'

8.2 手机号

-- 中国手机号
'^1[3-9][0-9]{9}$'

8.3 身份证号

-- 18 位身份证
'^[1-9][0-9]{16}[0-9Xx]$'

8.4 IP 地址

'^([0-9]{1,3}\.){3}[0-9]{1,3}$'

8.5 URL

'^https?://[A-Za-z0-9.-]+\.[A-Za-z]{2,}(/.*)?$'

8.6 日期

-- YYYY-MM-DD
'^[0-9]{4}-[0-9]{2}-[0-9]{2}$'

9. 应用场景

9.1 数据清洗

-- 清理电话号码
UPDATE customers 
SET phone = REGEXP_REPLACE(phone, '[^0-9]', '');

-- 标准化邮编
UPDATE addresses
SET zip = REGEXP_SUBSTR(zip, '[0-9]{5}');

9.2 数据验证

-- 验证邮箱
ALTER TABLE customers 
ADD CONSTRAINT chk_email 
CHECK (REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'));

9.3 数据提取

-- 从文本提取 URL
SELECT 
  id,
  REGEXP_SUBSTR(content, 'https?://[^\s]+') AS url
FROM articles;

9.4 数据脱敏

-- 脱敏身份证
SELECT 
  id,
  REGEXP_REPLACE(id_card, '([0-9]{4})[0-9]{8}([0-9]{4})', '\1********\2') AS masked
FROM customers;

10. 性能考虑

10.1 索引

正则表达式不能使用普通索引：

-- 函数索引
CREATE INDEX idx_emp_name_regex ON employees(REGEXP_SUBSTR(last_name, '^[A-Z]+'));

10.2 优化

-- 1. 简化正则
-- 2. 使用锚点 ^ $
-- 3. 避免回溯
-- 4. 使用 LIKE 替代简单匹配

11. 常见坑与排错

11.1 转义错误

-- 错误：. 匹配任意字符
WHERE REGEXP_LIKE(name, 'Mr.')

-- 修复：转义
WHERE REGEXP_LIKE(name, 'Mr\.')

11.2 大小写

-- 默认区分大小写
WHERE REGEXP_LIKE(name, 'smith')

-- 不区分
WHERE REGEXP_LIKE(name, 'smith', 'i')

11.3 性能差

修复：

-- 1. 简化正则
-- 2. 使用 LIKE
-- 3. 加函数索引
-- 4. 限制数据量

12. 最佳实践

  1. 简单匹配用 LIKE：性能好
  2. 复杂匹配用正则：灵活
  3. 使用锚点 ^ $：精确匹配
  4. 不区分大小写用 i：方便
  5. 函数索引：提升性能
  6. 数据验证用 CHECK：完整性
  7. 数据清洗用 REPLACE：批量处理
  8. 测试正则：验证正确性
  9. 避免复杂回溯：性能
  10. 参考文档：语法准确

13. 参考资料

[1] Oracle Database SQL Language Reference 19c, “Regular Expressions” https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Regular-Expression-Conditions.html