SQL中replace等正则表达式
正则表达式在数据处理中的应用
基础资源
FineReport 正则表达式文档
Oracle 正则函数详解
核心函数
REGEXP_INSTR - 查找位置
返回字符串中匹配正则表达式的位置。
-- 返回第一个元音字母的位置
SELECT REGEXP_INSTR('Itmyhome', 'a|i|o|e|u') FROM dual;
-- 结果:2 (字母 'I' 的位置)
REGEXP_REPLACE - 替换内容
使用正则表达式替换字符串内容。
-- 移除所有非数字字符
SELECT REGEXP_REPLACE('23456中国3-00=.,45', '[^0-9]') FROM dual;
-- 结果:234563045
实用案例
1. 提取字符串中的数字
方法一:使用 TRANSLATE 函数
-- 提取纯数字
SELECT TRANSLATE('1212中国2323', '0123456789' || '1212中国2323', '0123456789')
FROM dual;
-- 结果:12122323
方法二:使用 REGEXP_REPLACE 函数
-- 移除非数字字符
SELECT REGEXP_REPLACE('23456中国3-00=.,45', '[^0-9]')
FROM dual;
-- 结果:234563045
2. 提取数字(包括小数)
-- 提取数字和小数点
SELECT REGEXP_REPLACE('价格123.45元,折扣8.5%', '[^0-9.]', '')
FROM dual;
-- 结果:123.458.5
-- 更精确的小数提取
SELECT REGEXP_SUBSTR('价格123.45元,折扣8.5%', '\d+\.?\d*')
FROM dual;
-- 结果:123.45
3. 数据清洗:删除异常电话号码
删除包含非数字字符的电话号码记录:
DELETE FROM table_name
WHERE LENGTH(
TRANSLATE(
TRIM(phone_number),
'0123456789' || phone_number,
'0123456789'
)
) <> LENGTH(TRIM(phone_number));
说明:
TRANSLATE
函数移除数字字符后,如果长度发生变化,说明原字符串包含非数字字符- 此方法可有效识别和删除格式异常的电话号码
4. 常用正则模式
模式 | 说明 | 示例 |
---|---|---|
\d |
匹配数字 | \d+ 匹配一个或多个数字 |
[0-9] |
匹配数字字符 | [0-9]+ 等同于 \d+ |
[^0-9] |
匹配非数字字符 | 用于移除非数字内容 |
\d+\.?\d* |
匹配小数 | 123.45, 123, .45 |
[a-zA-Z] |
匹配字母 | 英文字母匹配 |
[^\x00-\x7F] |
匹配中文字符 | 用于处理中文内容 |
进阶应用
验证手机号格式
-- 验证11位手机号(以1开头)
SELECT phone_number
FROM users
WHERE REGEXP_LIKE(phone_number, '^1[3-9]\d{9}$');
提取邮箱域名
-- 从邮箱地址中提取域名
SELECT REGEXP_SUBSTR(email, '@(.+)', 1, 1, NULL, 1) AS domain
FROM users;
格式化身份证号
-- 验证18位身份证号格式
SELECT id_card
FROM users
WHERE REGEXP_LIKE(id_card, '^\d{17}[\dXx]$');
最佳实践
- 性能考虑:正则表达式比普通字符串函数耗时更多,大数据量时需谨慎使用
- 索引影响:在WHERE条件中使用正则会导致全表扫描
- 测试验证:复杂正则表达式建议先在小数据集上测试
- 可读性:复杂正则建议添加注释说明
总结
正则表达式在数据清洗、格式验证、内容提取等场景中非常有用,但是出场率相一般也不算高。