Oracle正则表达式实战：5个高频数据清洗场景与高效SQL模板
数据清洗是每个数据库从业者绕不开的日常任务。面对杂乱无章的原始数据，传统字符串函数往往需要多层嵌套和复杂逻辑，而Oracle正则表达式则能以更简洁、更强大的方式解决这些问题。本文将聚焦五个实际业务中最常见的数据清洗场景，提供可直接复用的SQL模板，并分享性能优化技巧。
1. 手机号脱敏与格式校验
在用户数据保护日益重要的今天，手机号脱敏成为数据共享前的必要步骤。同时，确保手机号格式正确也是数据质量的基础。
1.1 保留前3后4的脱敏处理
SELECT customer_name, REGEXP_REPLACE(phone_number, '(\d{3})\d{4}(\d{4})', '\1****\2') AS masked_phone FROM customer_table;这个模式匹配11位手机号，捕获前3位(\1)和后4位(\2)，中间4位替换为星号。相比传统的SUBSTR和CONCAT组合，正则方案更简洁且易于维护。
1.2 手机号格式校验
识别不符合规范的手机号：
SELECT phone_number, CASE WHEN REGEXP_LIKE(phone_number, '^1[3-9]\d{9}$') THEN '有效' ELSE '无效' END AS validity FROM customer_table;关键校验点：
- 以1开头(
^1) - 第二位是3-9(
[3-9]) - 后面跟随9位数字(
\d{9}) - 总长度11位(
$)
2. 邮箱地址的标准化处理
邮箱数据常见问题包括大小写混乱、空格干扰和无效格式。正则表达式能一站式解决这些问题。
2.1 统一小写并去除空格
UPDATE user_info SET email = LOWER(REGEXP_REPLACE(email, '\s', '')) WHERE REGEXP_LIKE(email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$');这个操作同时完成了三件事：
\s匹配所有空白字符并删除LOWER函数统一为小写REGEXP_LIKE确保只处理有效邮箱格式
2.2 提取邮箱域名分析
SELECT REGEXP_SUBSTR(email, '@(.+)$', 1, 1, '', 1) AS domain, COUNT(*) AS user_count FROM user_info GROUP BY REGEXP_SUBSTR(email, '@(.+)$', 1, 1, '', 1) ORDER BY user_count DESC;参数解析：
'@(.+)$'匹配@符号后的所有字符直到结尾- 最后一个参数
1表示返回第一个捕获组(括号内内容)
3. 地址信息结构化提取
非结构化的地址信息是数据清洗的难点。通过正则表达式，我们可以从中提取出有价值的结构化信息。
3.1 分离省市区信息
假设地址格式为"广东省深圳市南山区科技园路1号"：
SELECT address, REGEXP_SUBSTR(address, '^(.+?[省市自治区])') AS province, REGEXP_SUBSTR(address, '^.+?[省市自治区](.+?[市区县])') AS city, REGEXP_SUBSTR(address, '^.+?[省市自治区].+?[市区县](.+?(镇|乡|街道))') AS district FROM address_table;模式说明：
.+?非贪婪匹配任意字符[省市自治区]匹配行政区划关键词- 通过嵌套模式逐步提取下级区域
3.2 标准化道路地址
UPDATE address_table SET street = REGEXP_REPLACE( REGEXP_SUBSTR(address, '[^省市自治区]+(路|街|巷|道)\d+号?'), '\s+', '' );这个操作：
- 提取包含路名的部分(
[^省市自治区]+) - 匹配道路类型(
(路|街|巷|道)) - 包含门牌号(
\d+号?) - 去除中间所有空白(
\s+)
4. 非标准日期格式化
不同来源的日期数据格式各异，正则表达式能识别各种格式并转换为标准形式。
4.1 统一多种日期格式
SELECT raw_date, CASE WHEN REGEXP_LIKE(raw_date, '^\d{4}-\d{2}-\d{2}$') THEN TO_DATE(raw_date, 'YYYY-MM-DD') WHEN REGEXP_LIKE(raw_date, '^\d{2}/\d{2}/\d{4}$') THEN TO_DATE(raw_date, 'MM/DD/YYYY') WHEN REGEXP_LIKE(raw_date, '^\d{8}$') THEN TO_DATE(raw_date, 'YYYYMMDD') ELSE NULL END AS standardized_date FROM date_table;4.2 提取日期中的年月信息
SELECT REGEXP_REPLACE(raw_date, '.*?(\d{4}).*?(\d{2}).*', '\1-\2') AS year_month FROM date_table WHERE REGEXP_LIKE(raw_date, '\d{4}.*\d{2}');这个灵活的模式可以：
- 从各种格式中识别4位年份和2位月份
- 忽略中间的无关字符(
.*?) - 重组为YYYY-MM格式
5. 去除特殊字符与HTML标签
从网页或文档中提取的文本常包含需要清理的特殊字符和标记。
5.1 清理HTML标签
SELECT REGEXP_REPLACE(html_content, '<[^>]+>', '') AS plain_text FROM web_content;模式解析：
<[^>]+>匹配任何尖括号包围的内容[^>]+表示一个或多个非>字符
5.2 去除控制字符和特殊符号
UPDATE text_data SET clean_text = REGEXP_REPLACE(raw_text, '[\x00-\x1F\x7F[:punct:]]', ' ') WHERE REGEXP_LIKE(raw_text, '[\x00-\x1F\x7F[:punct:]]');这个操作：
\x00-\x1F匹配ASCII控制字符\x7F匹配删除字符[:punct:]POSIX字符类匹配所有标点- 替换为空格避免单词粘连
性能优化实战技巧
正则表达式虽然强大，但不合理使用会导致性能问题。以下是关键优化点：
- 预编译正则模式：对于频繁使用的模式，考虑使用PL/SQL函数封装
CREATE OR REPLACE FUNCTION extract_phone(text IN VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN REGEXP_SUBSTR(text, '1[3-9]\d{9}'); END;- 合理使用简单字符串函数：对于固定位置或简单模式，
SUBSTR或INSTR可能更快
-- 固定位置的区号提取 CASE WHEN phone LIKE '010%' THEN '010' WHEN phone LIKE '021%' THEN '021' ELSE SUBSTR(phone, 1, 4) END避免过度回溯：使用非贪婪匹配(
.*?)和具体字符类(\d代替.)创建函数索引：对频繁查询的正则条件创建函数索引
CREATE INDEX idx_valid_email ON users( CASE WHEN REGEXP_LIKE(email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$') THEN 1 ELSE 0 END );- 批量处理替代逐行处理：在PL/SQL中使用
FORALL批量应用正则操作
DECLARE TYPE id_array IS TABLE OF users.id%TYPE; v_ids id_array; BEGIN SELECT id BULK COLLECT INTO v_ids FROM users WHERE REGEXP_LIKE(email, '@gmail\.com$'); FORALL i IN 1..v_ids.COUNT UPDATE users SET email_type = 'gmail' WHERE id = v_ids(i); END;
