正则表达式匹配

正则表达式是处理复杂字符串模式匹配的强大工具，本篇介绍各数据库的实现方式。

📊 支持情况

1. 正则表达式基础

常用元字符

2. REGEXP / RLIKE 匹配

MySQL

-- 基本匹配
SELECT 'abc' REGEXP 'a.c';    -- 1（true）
SELECT 'abc' REGEXP '^a';     -- 1
SELECT 'abc' REGEXP 'c$';     -- 1
SELECT 'abc' REGEXP '[xyz]';  -- 0

-- 否定匹配
SELECT 'abc' NOT REGEXP '[0-9]';  -- 1（不含数字）

-- 在WHERE中使用
SELECT *
FROM users
WHERE email REGEXP '@gmail\\.com$';

Oracle

-- REGEXP_LIKE（返回布尔值）
SELECT 'abc' FROM dual WHERE REGEXP_LIKE('abc', 'a.c');
SELECT * FROM users WHERE REGEXP_LIKE(email, '@gmail\.com$');

Hive / MaxCompute

-- RLIKE
SELECT 'abc' RLIKE 'a.c';  -- true

-- 在WHERE中使用
SELECT * FROM users WHERE email RLIKE '.*@gmail\\.com$';

ClickHouse

SELECT match('abc', 'a.c');  -- 1

SELECT *
FROM users
WHERE email LIKE '%@gmail.com%';  -- 不支持正则LIKE

-- 使用正则匹配
SELECT *
FROM users
WHERE match(email, '@gmail\\.com$');

3. REGEXP_SUBSTR 提取

MySQL 8.0+

-- 提取匹配的子串
SELECT REGEXP_SUBSTR('abc123def456', '[0-9]+');  -- 123

-- 指定起始位置
SELECT REGEXP_SUBSTR('abc123def456', '[0-9]+', 1);     -- 123
SELECT REGEXP_SUBSTR('abc123def456', '[0-9]+', 7);     -- 456

-- occurrence（occurrence）
SELECT REGEXP_SUBSTR('abc123def456', '[0-9]+', 1, 2);  -- 456（第2次匹配）

-- 返回所有匹配（使用子查询）
SELECT REGEXP_SUBSTR('abc123def456', '[0-9]+', 1, 1) AS match1
UNION ALL
SELECT REGEXP_SUBSTR('abc123def456', '[0-9]+', 1, 2);

Oracle

-- 提取子串
SELECT REGEXP_SUBSTR('abc123def456', '[0-9]+') FROM dual;    -- 123

-- 指定位置和occurrence
SELECT REGEXP_SUBSTR('abc123def456', '[0-9]+', 1, 2) FROM dual;  -- 456

-- 子表达式
SELECT REGEXP_SUBSTR('abc@123.com', '([a-z]+)@([0-9]+)\\.com', 1, 1, NULL, 1) FROM dual;  -- abc
SELECT REGEXP_SUBSTR('abc@123.com', '([a-z]+)@([0-9]+)\\.com', 1, 1, NULL, 2) FROM dual;  -- 123

Hive

SELECT regexp_extract('abc123def456', '[0-9]+', 0);  -- 123

-- 子表达式（括号分组）
SELECT regexp_extract('user123@example.com', '([a-z]+)@([0-9]+)', 1);  -- user
SELECT regexp_extract('user123@example.com', '([a-z]+)@([0-9]+)', 2);  -- 123

ClickHouse

SELECT extractAll('abc123def456', '[0-9]+');  -- 返回数组 [123, 456]
SELECT extract('abc123def456', '[0-9]+');     -- 第一个匹配

4. REGEXP_REPLACE 替换

MySQL 8.0+

-- 替换所有匹配
SELECT REGEXP_REPLACE('a1b2c3', '[0-9]', '*');  -- a*b*c*

-- 替换指定次数
SELECT REGEXP_REPLACE('a1b2c3', '[0-9]', '*', 1, 2);  -- a*b2c3（只替换前2个）

-- 移除数字
SELECT REGEXP_REPLACE('price: $100', '[0-9]', '');  -- price: $

Oracle

SELECT REGEXP_REPLACE('a1b2c3', '[0-9]', '*') FROM dual;           -- a*b*c*
SELECT REGEXP_REPLACE('phone: 123-456-7890', '[0-9-]', '') FROM dual;  -- phone:

-- 使用子表达式
SELECT REGEXP_REPLACE('John Smith', '([a-z]+) ([a-z]+)', '\\2, \\1') FROM dual;  -- Smith, John

Hive

SELECT regexp_replace('a1b2c3', '[0-9]', '*');  -- a*b*c*
SELECT regexp_replace('phone: 123-456-7890', '[0-9-]', '');  -- phone:

ClickHouse

SELECT replaceAll('a1b2c3', '[0-9]', '*');   -- a*b*c*
SELECT replaceRegexpAll('a1b2c3', '[0-9]', '*');  -- 同上

5. REGEXP_COUNT 计数

MySQL 8.0+

-- 统计匹配次数
SELECT REGEXP_COUNT('a1b2c3d4', '[0-9]');  -- 4

-- 起始位置
SELECT REGEXP_COUNT('a1b2c3d4', '[0-9]', 3);  -- 3（从第3个字符开始）

Oracle

SELECT REGEXP_COUNT('a1b2c3d4', '[0-9]') FROM dual;  -- 4
SELECT REGEXP_COUNT('ababab', 'ab') FROM dual;       -- 3

6. 实战案例

场景1：提取手机号

-- MySQL 8.0+
SELECT REGEXP_SUBSTR('联系电话：13812345678 或 13987654321', '1[3-9][0-9]{9}') AS phone;

场景2：验证邮箱格式

-- MySQL
SELECT email
FROM users
WHERE email NOT REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$';

-- Oracle
SELECT email
FROM users
WHERE NOT REGEXP_LIKE(email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$');

场景3：提取URL域名

-- MySQL 8.0+
SELECT
    url,
    REGEXP_SUBSTR(url, 'https?://([^/]+)', 1, 1, NULL, 1) AS domain
FROM web_logs;

-- Hive
SELECT
    url,
    regexp_extract(url, 'https?://([^/]+)', 1) AS domain
FROM web_logs;

场景4：数据清洗

-- 去除特殊字符
SELECT REGEXP_REPLACE(name, '[^a-zA-Z0-9\u4e00-\u9fff]', '') AS clean_name
FROM users;

-- 统一日期格式
SELECT REGEXP_REPLACE(date_str, '(\\d{4})年(\\d{2})月(\\d{2})日', '\\1-\\2-\\3') AS date_formatted
FROM raw_data;

场景5：密码强度验证

-- Oracle
SELECT *
FROM users
WHERE NOT REGEXP_LIKE(password,
    '^(?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.*[!@#$%^&*()]).{8,}$');
-- 必须包含：大写、小写、数字、特殊字符，且至少8位

7. 正则表达式速查表

常用模式

⚠️ 注意事项

1. 转义字符

-- MySQL中正则的\需要双反斜杠
SELECT REGEXP_SUBSTR('price: $100', '\\$[0-9]+');  -- $100

-- Hive中需要单反斜杠
SELECT regexp_extract('price: $100', '\\$[0-9]+', 0);  -- $100

-- Oracle中也需要转义
SELECT REGEXP_SUBSTR('price: $100', '\\$[0-9]+') FROM dual;

2. 性能问题

-- ❌ 避免：在LIKE能解决的情况下使用正则
SELECT * FROM users WHERE email LIKE '%@gmail.com%';
-- ✅ 优于
SELECT * FROM users WHERE email REGEXP '@gmail\\.com$';

-- 正则匹配可能会全表扫描，大数据量时谨慎使用

3. NULL处理

-- REGEXP相关函数遇到NULL返回NULL
SELECT REGEXP_SUBSTR(NULL, '[0-9]');  -- NULL

-- 建议使用COALESCE
SELECT COALESCE(REGEXP_SUBSTR(col, '[0-9]+'), '') AS result
FROM table;

📝 练习题

-- 建表
CREATE TABLE logs (
    id INT,
    message STRING
);

INSERT INTO logs VALUES
(1, 'User login from IP 192.168.1.100 at 2024-01-14 10:30:00'),
(2, 'Error: File not found - /var/log/error.log'),
(3, 'API request from 10.0.0.55 completed in 234ms'),
(4, 'Warning: Memory usage above 80%');

要求：

1. 提取所有IP地址
2. 提取时间戳
3. 提取数字（内存百分比等）
4. 过滤包含Error的记录

-- 参考答案
-- 1. 提取IP
SELECT
    id,
    REGEXP_SUBSTR(message, '[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}') AS ip
FROM logs;

-- 2. 提取时间戳
SELECT
    id,
    REGEXP_SUBSTR(message, '\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}') AS timestamp
FROM logs;

-- 3. 提取数字
SELECT
    id,
    REGEXP_SUBSTR(message, '[0-9]+') AS number_found
FROM logs;

-- 4. 过滤Error
SELECT *
FROM logs
WHERE message REGEXP 'Error';