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]$');

最佳实践

  1. 性能考虑:正则表达式比普通字符串函数耗时更多,大数据量时需谨慎使用
  2. 索引影响:在WHERE条件中使用正则会导致全表扫描
  3. 测试验证:复杂正则表达式建议先在小数据集上测试
  4. 可读性:复杂正则建议添加注释说明

总结

正则表达式在数据清洗、格式验证、内容提取等场景中非常有用,但是出场率相一般也不算高。

posted @ 2025-05-29 21:45  灯熄帘摇月候身  阅读(110)  评论(0)    收藏  举报