Oracle数据库数据编程SQL＜5 正则表达式函数*****＞

Oracle 提供了一组强大的正则表达式函数，用于在 SQL 和 PL/SQL 中进行复杂的模式匹配和文本处理。这些函数基于 POSIX 标准正则表达式，功能强大且灵活。

目录

一、Oracle 正则表达式函数概览

二、函数详解及示例

1. REGEXP_LIKE

2. REGEXP_INSTR

3. REGEXP_SUBSTR

4. REGEXP_REPLACE

5. REGEXP_COUNT

三、正则表达式模式元素

四、高级正则表达式技巧

1. 后向引用

2. 非贪婪匹配

3. 零宽断言

五、性能考虑

六、综合应用示例 

1. 数据验证

2. 数据清洗

3. 复杂文本分析

七、各Oracle版本的正则表达式支持

---

一、Oracle 正则表达式函数概览

Oracle 主要提供以下正则表达式函数：

1. REGEXP_LIKE：检查字符串是否匹配正则表达式模式
2. REGEXP_INSTR：返回匹配子串的位置
3. REGEXP_SUBSTR：提取匹配的子串
4. REGEXP_REPLACE：替换匹配的子串
5. REGEXP_COUNT (11g+)：计算匹配出现的次数

二、函数详解及示例

1. REGEXP_LIKE

【功能：】检查字符串是否匹配指定的正则表达式模式，返回满足条件的字段【语法：】
REGEXP_LIKE(source_string, pattern [, match_parameter])
regexp_like(列名,匹配模式,匹配行为)
由正则表达式构成匹配模式，长度在512个字节内【参数：】
source_string：要搜索的字符串
pattern：正则表达式模式
match_parameter：可选，修改匹配行为，可包含以下字符：'i'：不区分大小写'c'：区分大小写'n'：允许点号(.)匹配换行符'm'：多行模式【示例：】
-- 检查字符串是否包含数字
SELECT * FROM employees 
WHERE REGEXP_LIKE(job_id, '[0-9]');-- 不区分大小写匹配
SELECT * FROM customers 
WHERE REGEXP_LIKE(name, '^john', 'i');

2. REGEXP_INSTR

【功能：】返回匹配子字符串的起始或结束位置；返回满足条件的字符或字符串的位置【语法：】
REGEXP_INSTR(source_string, pattern [, start_position [, occurrence [, return_option [, match_parameter [, subexpression]]]])
regexp_instr(列名,匹配模式,数1,数2,匹配行为)--从数1位开始查，查第数2次匹配的位置【参数：】
start_position：开始搜索的位置，默认为1
occurrence：    要查找的第几个匹配项，默认为1
return_option：0：返回匹配的第一个字符位置（默认）1：返回匹配后的下一个字符位置
subexpression： 对于有子表达式的模式，指定哪个子表达式【示例：】
-- 查找第一个数字出现的位置
SELECT REGEXP_INSTR('Order 12345', '[0-9]') FROM dual;
-- 结果：7-- 查找第二个数字出现的位置
SELECT REGEXP_INSTR('Order 12345', '[0-9]', 1, 2) FROM dual;
-- 结果：8-- 查找电子邮件中的域名位置
SELECT REGEXP_INSTR('user@example.com', '@[^.]+\\.') FROM dual;

3. REGEXP_SUBSTR

【功能：】提取匹配正则表达式的子字符串【语法：】
REGEXP_SUBSTR(source_string, pattern [, start_position [, occurrence [, match_parameter [, subexpression]]]])
regexp_substr(列名,匹配模式，数1，数2,匹配行为)【示例：】
-- 提取字符串中的第一个数字序列
SELECT REGEXP_SUBSTR('Order 12345 for 678 items', '[0-9]+') FROM dual;
-- 结果：12345-- 提取电子邮件中的用户名
SELECT REGEXP_SUBSTR('user@example.com', '^[^@]+') FROM dual;
-- 结果：user-- 提取第三个由空格分隔的单词
SELECT REGEXP_SUBSTR('Oracle Database 12c', '[^ ]+', 1, 3) FROM dual;
-- 结果：12c

4. REGEXP_REPLACE

【功能：】替换匹配正则表达式的子字符串【语法：】
REGEXP_REPLACE(source_string, pattern [, replace_string [, start_position [, occurrence [, match_parameter]]])
regexp_replace(列名,匹配模式，字符,数1，数2，匹配行为)【示例：】
-- 隐藏信用卡号中间数字
SELECT REGEXP_REPLACE('Card: 1234-5678-9012-3456', '([0-9]{4})-([0-9]{2})[0-9]{2}-([0-9]{4})', '\1-XX-XX-\3') FROM dual;
-- 结果：Card: 1234-XX-XX-3456-- 标准化日期格式
SELECT REGEXP_REPLACE('31/12/2023', '([0-9]{2})/([0-9]{2})/([0-9]{4})', '\3-\2-\1') FROM dual;
-- 结果：2023-12-31-- 移除所有非字母字符
SELECT REGEXP_REPLACE('A1B2C3', '[^A-Za-z]', '') FROM dual;
-- 结果：ABC

5. REGEXP_COUNT

【功能：】计算正则表达式模式出现的次数（Oracle 11g+）【语法：】
REGEXP_COUNT(source_string, pattern [, start_position [, match_parameter]])
regexp_count(列名,匹配模式,数1,匹配行为)--从数1位开始查【示例：】
-- 计算字符串中数字出现的次数
SELECT REGEXP_COUNT('Order 12345 for 678 items', '[0-9]') FROM dual;
-- 结果：8-- 计算单词"the"出现的次数（不区分大小写）
SELECT REGEXP_COUNT('The quick brown fox jumps over the lazy dog', '\bthe\b', 1, 'i') FROM dual;
-- 结果：2

三、正则表达式模式元素

Oracle 支持的标准正则表达式元字符：

元字符	描述	示例
^	字符串开始，在[]外面定位到行首，在[]里面是非的意思	^A 匹配以A开头的行
$	字符串结束，定位到行尾	end$ 匹配以end结尾的行
.	匹配任意单个字符(除换行符)匹配除null以外的任意单个字符	a.c 匹配abc, aXc等
/	转义
*	匹配前一个元素0次或多次	ab*c 匹配ac, abc, abbc等
+	匹配前一个元素1次或多次	ab+c 匹配abc, abbc但不匹配ac
?	匹配前一个元素0次或1次	ab?c 匹配ac或abc
[abc]	匹配abc中的任意单个字符
[a-z]	匹配a-z的任意单个字符
{n}	精确匹配n次	a{3} 匹配aaa
{n,}	匹配至少n次	a{2,} 匹配aa, aaa等
{n,m}	匹配n到m次	a{2,4} 匹配aa, aaa或aaaa
[...]	字符集合	[abc] 匹配a, b或c
[^...]	否定字符集合	[^abc] 匹配非a, b, c的字符
|	或操作	a|b 匹配a或b
()	分组,将子表达式分组为一个替换单元，量词单元或后者引用	(ab)+ 匹配ab, abab等
\d	数字字符，等同于[0-9]	\d+ 匹配一个或多个数字
\D	非数字字符，等同于[^0-9]	\D+ 匹配一个或多个非数字
\w	单词字符，等同于[a-zA-Z0-9_]	\w+ 匹配一个单词
\W	非单词字符，等同于[^a-zA-Z0-9_]	\W+ 匹配非单词字符
\s	空白字符(空格,制表符,换行等)	\s+ 匹配一个或多个空白
\S	非空白字符	\S+ 匹配一个或多个非空白
\b	单词边界	\bthe\b 匹配单词the
[:alpha:]	字母（包含所有大小写）
[:lower:]	小写字母a-z
[:upper:]	大写字母A-Z
[:digit:]	数字0-9
[:alphanum:]	所有字母和数字
[:space:]	空白字符（禁止打印）如回车、换行、换页
[:punct:]	标点字符
[:cntrl:]	控制字符（禁止打印）
[:print:]	可打印字符，经常和分组符一起使用

四、高级正则表达式技巧

1. 后向引用

使用括号分组后，可以用\1到\9引用前面的分组：

-- 查找重复单词
SELECT REGEXP_SUBSTR('the the quick brown fox', '\b(\w+)\s+\1\b', 1, 1, 'i') 
FROM dual;
-- 结果：the the-- 交换名字和姓氏
SELECT REGEXP_REPLACE('John Smith', '(.*) (.*)', '\2, \1') FROM dual;
-- 结果：Smith, John

2. 非贪婪匹配

默认情况下，量词(*, +, ?, {n,m})是"贪婪"的，会匹配尽可能多的字符。添加?使其变为非贪婪：

-- 贪婪匹配
SELECT REGEXP_SUBSTR('<b>bold</b> and <i>italic</i>', '<.*>') FROM dual;
-- 结果：<b>bold</b> and <i>italic</i>-- 非贪婪匹配
SELECT REGEXP_SUBSTR('<b>bold</b> and <i>italic</i>', '<.*?>') FROM dual;
-- 结果：<b>

3. 零宽断言

断言	描述	示例
(?=...)	正向肯定预查	\w+(?=ing) 匹配以ing结尾的单词前部分
(?!...)	正向否定预查	\d{3}(?!\d) 匹配后面不跟数字的3位数字
(?<=...)	反向肯定预查	(?<=\$)\d+ 匹配$后面的数字
(?<!...)	反向否定预查	(?<!\$)\d+ 匹配前面不是$的数字

示例：

-- 匹配价格数字（$后面跟着数字）
SELECT REGEXP_SUBSTR('Price: $123.45', '(?<=\$)[0-9.]+') FROM dual;
-- 结果：123.45-- 匹配不以"un"开头的单词
SELECT REGEXP_SUBSTR('happy unhappy reuse', '\b(?!un)\w+\b', 1, 2) FROM dual;
-- 结果：reuse

五、性能考虑

1. 简单模式优先：能用简单字符串函数(如INSTR, SUBSTR)解决的问题，不要用正则表达式
2. 避免过度使用：正则表达式比普通字符串函数消耗更多CPU资源
3. 预编译模式：在PL/SQL中频繁使用的模式，可以考虑预编译：

   DECLAREpattern VARCHAR2(100) := '^[A-Za-z]+$';
   BEGINIF REGEXP_LIKE(input_string, pattern) THEN-- 处理逻辑END IF;
   END;

4. 合理使用匹配参数：如不需要区分大小写，使用'i'参数比[Aa]这样的模式更高效

六、综合应用示例 

1. 数据验证

-- 验证电子邮件格式
SELECT email 
FROM customers
WHERE REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');-- 验证电话号码格式(北美格式)
SELECT phone_number 
FROM contacts
WHERE REGEXP_LIKE(phone_number, '^\(?\d{3}\)?[- ]?\d{3}[- ]?\d{4}$');

2. 数据清洗

-- 从文本中提取所有URL
SELECT REGEXP_SUBSTR(web_content, 'https?://[[:alnum:]_#?%&/=~\.-]+', 1, LEVEL) AS url
FROM web_pages
CONNECT BY REGEXP_SUBSTR(web_content, 'https?://[[:alnum:]_#?%&/=~\.-]+', 1, LEVEL) IS NOT NULL;-- 标准化产品代码
UPDATE products
SET product_code = REGEXP_REPLACE(product_code, '^([A-Z]{2})([0-9]{2})([0-9]{4})$', '\1-\2-\3')
WHERE REGEXP_LIKE(product_code, '^[A-Z]{2}[0-9]{6}$');

3. 复杂文本分析

-- 分析日志文件中的错误消息
SELECT REGEXP_SUBSTR(log_entry, '\[ERROR\] (.+?) at', 1, 1, 'i', 1) AS error_message,REGEXP_SUBSTR(log_entry, 'at (.+?):(\d+)', 1, 1, 'i', 1) AS file_name,REGEXP_SUBSTR(log_entry, 'at (.+?):(\d+)', 1, 1, 'i', 2) AS line_number,log_time
FROM application_logs
WHERE REGEXP_LIKE(log_entry, '\[ERROR\]');

七、各Oracle版本的正则表达式支持

1. Oracle 10g：引入了基本正则表达式函数(REGEXP_LIKE, REGEXP_INSTR, REGEXP_SUBSTR, REGEXP_REPLACE)
2. Oracle 11g：新增REGEXP_COUNT函数
3. Oracle 12c：增强了正则表达式性能，支持更多Perl风格的正则表达式特性

通过掌握这些正则表达式函数，您可以在Oracle数据库中执行复杂的文本处理、数据验证和转换操作，大大增强了SQL处理文本数据的能力。