Oracle REGEXP_SUBSTR, REGEXP_INSTR, REGEXP_REPLACE, REGEXP_LIKE

我将详细讲解 Oracle 中这四个核心的正则表达式函数，并通过对比和示例展示它们的区别和用法。

Oracle 正则表达式函数详解

1. REGEXP_SUBSTR - 提取子字符串

功能

从字符串中提取匹配正则表达式的子字符串。

语法

REGEXP_SUBSTR(
  source_string, 
  pattern, 
  [start_position], 
  [nth_occurrence], 
  [match_parameter], 
  [subexpression]
)

详细示例

基础提取

-- 提取第一个数字序列
SELECT REGEXP_SUBSTR('订单号: 12345, 金额: 678元', '\d+') AS result FROM dual;
-- 结果: 12345

-- 提取第二个数字序列
SELECT REGEXP_SUBSTR('订单号: 12345, 金额: 678元', '\d+', 1, 2) AS result FROM dual;
-- 结果: 678

使用子表达式（分组）

-- 提取邮箱用户名和域名
SELECT 
  REGEXP_SUBSTR('john.doe@example.com', '([^@]+)@(.+)', 1, 1, NULL, 1) AS username,
  REGEXP_SUBSTR('john.doe@example.com', '([^@]+)@(.+)', 1, 1, NULL, 2) AS domain
FROM dual;
-- 结果: john.doe | example.com

复杂模式提取

-- 提取URL的协议和域名
SELECT 
  REGEXP_SUBSTR('https://www.example.com/path', '([a-z]+)://([^/]+)', 1, 1, 'i', 1) AS protocol,
  REGEXP_SUBSTR('https://www.example.com/path', '([a-z]+)://([^/]+)', 1, 1, 'i', 2) AS domain
FROM dual;
-- 结果: https | www.example.com

2. REGEXP_INSTR - 返回匹配位置

功能

返回匹配正则表达式的子字符串的位置。

语法

REGEXP_INSTR(
  source_string, 
  pattern, 
  [start_position], 
  [nth_occurrence], 
  [return_option], 
  [match_parameter], 
  [subexpression]
)

参数说明

• return_option:
  • 0: 返回匹配开始位置（默认）
  • 1: 返回匹配结束位置的下一个位置

详细示例

查找位置

-- 查找第一个数字的开始位置
SELECT REGEXP_INSTR('价格: 999元, 折扣: 88折', '\d+') AS pos FROM dual;
-- 结果: 4 (数字9开始的位置)

-- 查找第一个数字的结束位置
SELECT REGEXP_INSTR('价格: 999元, 折扣: 88折', '\d+', 1, 1, 1) AS pos FROM dual;
-- 结果: 7 (数字9结束的下一个位置，即"元"的位置)

查找第n次出现的位置

-- 查找第二个数字序列的开始位置
SELECT REGEXP_INSTR('A1 B22 C333 D4444', '\d+', 1, 2) AS pos FROM dual;
-- 结果: 5 (B22中的2开始的位置)

-- 查找第二个数字序列的结束位置
SELECT REGEXP_INSTR('A1 B22 C333 D4444', '\d+', 1, 2, 1) AS pos FROM dual;
-- 结果: 7 (B22中的2结束的下一个位置)

实用场景：提取子字符串的起始位置

SELECT 
  product_desc,
  REGEXP_INSTR(product_desc, '\d+[xX*]\d+') AS size_start_pos,
  REGEXP_SUBSTR(product_desc, '\d+[xX*]\d+') AS size
FROM products;
-- 可用于提取"100x200"这样的尺寸信息

3. REGEXP_REPLACE - 搜索和替换

功能

使用正则表达式搜索并替换字符串内容。

语法

REGEXP_REPLACE(
  source_string, 
  pattern, 
  replacement_string, 
  [start_position], 
  [nth_occurrence], 
  [match_parameter]
)

详细示例

数据脱敏

-- 手机号脱敏
SELECT REGEXP_REPLACE('13812345678', '(\d{3})\d{4}(\d{4})', '\1****\2') AS masked_phone FROM dual;
-- 结果: 138****5678

-- 邮箱脱敏
SELECT REGEXP_REPLACE('john.doe@example.com', '(\w{3})[^@]*@(\w{3})', '\1***@\2***') AS masked_email FROM dual;
-- 结果: joh***@exa***

数据清洗

-- 移除所有非数字字符
SELECT REGEXP_REPLACE('电话: (555)-123-4567 ext.890', '[^0-9]', '') AS clean_number FROM dual;
-- 结果: 5551234567890

-- 标准化日期格式
SELECT REGEXP_REPLACE('2023/01/01', '(\d{4})/(\d{1,2})/(\d{1,2})', '\1-\2-\3') AS formatted_date FROM dual;
-- 结果: 2023-01-01

复杂替换

-- 将驼峰命名转换为下划线命名
SELECT REGEXP_REPLACE('firstName', '([a-z])([A-Z])', '\1_\2') AS snake_case FROM dual;
-- 结果: first_name

SELECT REGEXP_REPLACE('getHTTPRequest', '([a-z])([A-Z])', '\1_\2') FROM dual;
-- 结果: get_H_T_T_P_Request (需要进一步处理)

4. REGEXP_LIKE - 条件匹配

功能

在 WHERE 子句中检查字符串是否匹配正则表达式模式。

语法

REGEXP_LIKE(
  source_string, 
  pattern, 
  [match_parameter]
)

详细示例

数据验证

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

-- 验证手机号格式
SELECT phone 
FROM customers 
WHERE REGEXP_LIKE(phone, '^1[3-9]\d{9}$');

模式搜索

-- 查找包含版本号的记录
SELECT file_name 
FROM documents 
WHERE REGEXP_LIKE(file_name, 'v\d+\.\d+(\.\d+)?');

-- 查找包含颜色的产品
SELECT product_name 
FROM products 
WHERE REGEXP_LIKE(product_name, '红|蓝|绿|黄|黑|白', 'i');

复杂条件查询

-- 查找符合复杂密码策略的用户
SELECT username 
FROM users 
WHERE REGEXP_LIKE(password, '^(?=.*[A-Z])(?=.*[a-z])(?=.*\d)(?=.*[@#$%]).{8,}$');

-- 查找特定格式的订单号
SELECT order_id 
FROM orders 
WHERE REGEXP_LIKE(order_id, '^ORD-\d{4}-[A-Z]{2}-\d{3}$');

函数对比总结

综合应用示例

场景：解析日志文件

SELECT 
  log_entry,
  -- 提取IP地址
  REGEXP_SUBSTR(log_entry, '\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}') AS ip_address,
  -- 提取时间戳位置
  REGEXP_INSTR(log_entry, '\[\d{2}/\w{3}/\d{4}:\d{2}:\d{2}:\d{2}') AS timestamp_pos,
  -- 提取HTTP状态码
  REGEXP_SUBSTR(log_entry, 'HTTP/\d\.\d"\s+(\d{3})', 1, 1, NULL, 1) AS http_status,
  -- 清理多余空格
  REGEXP_REPLACE(log_entry, '\s+', ' ') AS cleaned_entry
FROM server_logs
WHERE REGEXP_LIKE(log_entry, '^(\d{1,3}\.){3}\d{1,3}');

场景：数据标准化处理

UPDATE employees 
SET 
  phone = REGEXP_REPLACE(phone, '[^0-9]', ''),
  email = LOWER(REGEXP_REPLACE(email, '\s+', '')),
  employee_id = REGEXP_REPLACE(employee_id, '[^A-Z0-9]', '')
WHERE REGEXP_LIKE(phone, '[^0-9]') 
   OR REGEXP_LIKE(email, '[A-Z]') 
   OR REGEXP_LIKE(employee_id, '[^A-Z0-9]');

性能建议

1. 尽量使用简单模式：复杂的正则表达式会影响性能
2. 避免过度使用：在大量数据上使用正则表达式可能很慢
3. 考虑创建函数索引：对经常用于查询的列创建基于函数的索引
4. 优先使用字符串函数：对于简单模式，INSTR、SUBSTR 通常比正则表达式更快

-- 创建函数索引示例
CREATE INDEX idx_email_pattern ON users(REGEXP_SUBSTR(email, '@(.+)$'));

这四个函数提供了强大的文本处理能力，特别适合处理复杂的数据清洗、验证和提取任务。掌握它们可以极大地提高处理非结构化数据的效率。