SQL 正则函数

1. 正则函数概述

Oracle 提供了以下正则函数：

• REGEXP_LIKE：用于匹配字符串，通常作为 WHERE 条件。
• REGEXP_REPLACE：用于替换匹配到的字符。
• REGEXP_SUBSTR：用于截取匹配到的字符。
• REGEXP_INSTR：用于返回匹配字符的位置。

2. REGEXP_LIKE：匹配字符串

REGEXP_LIKE 用于判断字符串是否匹配指定的正则表达式。

示例：

-- 查找员工姓名以 "L" 开头并以 "K" 结尾的员工
SELECT ename
FROM tmp_emp
WHERE REGEXP_LIKE(ename, '^.L.*K$');

-- 查找员工编号为 4 位数字的员工
SELECT empno
FROM tmp_emp
WHERE REGEXP_LIKE(empno, '\d{4}')
ORDER BY empno;

-- 查找员工姓名为全英文的员工
SELECT 1
FROM dual
WHERE REGEXP_LIKE('hahaada09', '^[a-zA-Z]+$');

-- 查找员工姓名以 "a" 开头（不区分大小写）的员工
SELECT ename
FROM tmp_emp
WHERE REGEXP_LIKE(ename, '^a', 'i');

-- 查找员工姓名为全英文且以 "N" 结尾的员工
SELECT ename
FROM tmp_emp
WHERE REGEXP_LIKE(ename, '^[a-zA-Z]+N$');

正则表达式说明：

• ^：匹配字符串的开头。
• $：匹配字符串的结尾。
• .：匹配任意单个字符。
• *：匹配前面的字符 0 次或多次。
• \d：匹配数字。
• {4}：匹配前面的字符恰好 4 次。
• [a-zA-Z]：匹配任意字母。
• i：不区分大小写。

3. REGEXP_INSTR：返回匹配字符的位置

REGEXP_INSTR 用于返回匹配字符的位置。

示例：

-- 查找字符串中第一个非数字字符的位置
SELECT REGEXP_INSTR('13哎8te11电话', '\D') FROM dual; -- 结果：3

-- 从第 3 个字符开始，查找字符串中第 2 个非数字字符的位置
SELECT REGEXP_INSTR('13哎8te11电话', '[^0-9]', 3, 2) FROM dual; -- 结果：6

-- 从第 3 个字符开始，查找字符串中第 2 个非数字字符的下一个字符位置
SELECT REGEXP_INSTR('sun4zhu5an', '\D', 3, 2, 1) FROM dual; -- 结果：8

参数说明：

• 第 1 个参数：源字符串。
• 第 2 个参数：正则表达式。
• 第 3 个参数：开始查找的位置。
• 第 4 个参数：查找第几个匹配项。
• 第 5 个参数：返回匹配项的开始位置（0）或下一个字符的位置（1）。

4. REGEXP_REPLACE：替换匹配字符

REGEXP_REPLACE 用于替换匹配到的字符。

示例：

-- 将员工姓名中第 2 个非字母字符替换为 "A"
SELECT ename, REGEXP_REPLACE(ename, '[^A-Za-z]', 'A', 1, 2)
FROM tmp_emp
ORDER BY ename DESC;

-- 替换员工姓名中 "S" 到 "T" 之间的字符为 "="
SELECT ename,
       REGEXP_REPLACE(ename, 'S.*T', '=') AS "S.*T",
       REGEXP_REPLACE(ename, 'S..T', '=') AS "S..T"
FROM tmp_emp
WHERE REGEXP_LIKE(ename, 'S..T');

参数说明：

• 第 1 个参数：源字符串。
• 第 2 个参数：正则表达式。
• 第 3 个参数：替换的字符串。
• 第 4 个参数：开始查找的位置。
• 第 5 个参数：替换第几个匹配项。

5. REGEXP_SUBSTR：截取匹配字符

REGEXP_SUBSTR 用于截取匹配到的字符。

示例：

-- 从字符串中截取第一个数字
SELECT REGEXP_SUBSTR('1wzs7,18nz2hb3', '[\d+]', 1, 1) FROM dual; -- 结果：'1'

参数说明：

• 第 1 个参数：源字符串。
• 第 2 个参数：正则表达式。
• 第 3 个参数：开始查找的位置。
• 第 4 个参数：截取第几个匹配项。

6. 综合示例

以下是一些综合示例，展示了正则函数的实际应用。

（1）查找员工姓名中包含 “S” 和 “T” 的员工

SELECT ename
FROM tmp_emp
WHERE REGEXP_LIKE(ename, 'S.*T');

（2）替换员工姓名中的非字母字符

SELECT ename, REGEXP_REPLACE(ename, '[^A-Za-z]', 'A')
FROM tmp_emp;

（3）截取字符串中的数字

SELECT REGEXP_SUBSTR('1wzs7,18nz2hb3', '[\d+]', 1, 1) FROM dual;

总结

1. REGEXP_LIKE：用于匹配字符串。
2. REGEXP_INSTR：用于返回匹配字符的位置。
3. REGEXP_REPLACE：用于替换匹配字符。
4. REGEXP_SUBSTR：用于截取匹配字符。