2014-11-30 Created By BaoXinjian
一、摘要
ORACLE中的支持正则表达式的函数主要有下面四个：
1 REGEXP_LIKE ：与LIKE的功能相似
2 REGEXP_INSTR ：与INSTR的功能相似
3 REGEXP_SUBSTR ：与SUBSTR的功能相似
4 REGEXP_REPLACE ：与REPLACE的功能相似
它们在用法上与Oracle SQL 函数LIKE、INSTR、SUBSTR 和REPLACE 用法相同。
二、调用格式
1. 格式：
REGEXP_INSTR (source_string, pattern
[, position
[, occurrence
[, return_option
[, match_parameter ]
]
]
]
)
2. 参数说明：
(1). source_string：输入的字符串
(2). pattern：正则表达式
(3). position：标识从第几个字符开始正则表达式匹配。（默认为1）
(4). occurrence：标识第几个匹配组。（默认为1）
(5). return_option：0——返回第一个字符出现的位置。1：pattern下一个字符起始位置。
(6). match_parameter：取值范围
- i：大小写不敏感；
- c：大小写敏感；
- n：点号 . 不匹配换行符号；
- m：多行模式；
- x：扩展模式，忽略正则表达式中的空白字符。
2. REGEXP_LIKE / REGEXP_REPLACE / REGEXP_SUBSTR / REGEXP_COUNT 格式
三、元字符和字符簇
1. 特殊字符
'^' 匹配输入字符串的开始位置，在方括号表达式中使用，此时它表示不接受该字符集合。
'$' 匹配输入字符串的结尾位置。如果设置了 RegExp 对象的 Multiline 属性，则 $ 也匹配 '\n' 或 '\r'。
'.' 匹配除换行符 \n之外的任何单字符。
'?' 匹配前面的子表达式零次或一次。
'*' 匹配前面的子表达式零次或多次。
'+' 匹配前面的子表达式一次或多次。
'( )' 标记一个子表达式的开始和结束位置。
'[]' 标记一个中括号表达式。
'{m,n}' 一个精确地出现次数范围，m=<出现次数<=n，'{m}'表示出现m次，'{m,}'表示至少出现m次。
'|' 指明两项之间的一个选择。例子'^([a-z]+|[0-9]+)$'表示所有小写字母或数字组合成的字符串。
\num 匹配 num，其中 num 是一个正整数。对所获取的匹配的引用。
'\' 转义符。
2. 字符簇
[[:alpha:]] 任何字母。
[[:digit:]] 任何数字。
[[:alnum:]] 任何字母和数字。
[[:space:]] 任何白字符。
[[:upper:]] 任何大写字母。
[[:lower:]] 任何小写字母。
[[unct:]] 任何标点符号。
[[:xdigit:]] 任何16进制的数字，相当于[0-9a-fA-F]。
3. 各种操作符的运算优先级
\ 转义符
(), (?, (?=), [] 圆括号和方括号
*, +, ?, {n}, {n,}, {n,m} 限定符
^, $, \anymetacharacter 位置和顺序
| “或”操作
四、简单案例
1. 创建测试数据
CREATE TABLE test (mc VARCHAR2 (60));
BEGIN
INSERT INTO test VALUES ('112233445566778899');
INSERT INTO test VALUES ('22113344 5566778899');
INSERT INTO test VALUES ('33112244 5566778899');
INSERT INTO test VALUES ('44112233 5566 778899');
INSERT INTO test VALUES ('5511 2233 4466778899');
INSERT INTO test VALUES ('661122334455778899');
INSERT INTO test VALUES ('771122334455668899');
INSERT INTO test VALUES ('881122334455667799');
INSERT INTO test VALUES ('991122334455667788');
INSERT INTO test VALUES ('aabbccddee');
INSERT INTO test VALUES ('bbaaaccddee');
INSERT INTO test VALUES ('ccabbddee');
INSERT INTO test VALUES ('ddaabbccee');
INSERT INTO test VALUES ('eeaabbccdd');
INSERT INTO test VALUES ('ab123');
INSERT INTO test VALUES ('123xy');
INSERT INTO test VALUES ('007ab');
INSERT INTO test VALUES ('abcxy');
INSERT INTO test VALUES ('The final test is is is how to find duplicate words.');
COMMIT;
END;
2. 案例 REGEXP_LIKE
(1). {}的意思是连续几个的匹配上面的sql的意思是开头有1个到3个连续a
(2). 有连续3个a
(3). 以a开头以e结尾的字符之所以有.是因为如果写成^a*e$就变成以a开头中间都是a以e结尾的字符拉 *的意思是匹配它前面的字符
^ab*e$可否查出以a开头以e结尾的字符呢?
答案是否定的因为*虽然是可以匹配前面的b0次或者多次但是它也仅仅是匹配
以a开头中间可以有b并且都是b,或者中间没有字符,以e结尾的字符.
'^[[:alpha:]]+$'的意思是以字母从开头到结尾都包含字母的字符
(4). 小写字母或者数字开头的字符
(5). 小写字母开头的字符
(6). "^"就是一个有多种意义的字符元,主要看语意环境如果"^"是字符列中的第一个字符，就表示对这个字符串取反，因此, [^[:digit:]]就是表示查找非数字的模式即字符中不都是数字
(7). 以非数字开头的字符
3. 案例 REGEXP_INSTR
4. 案例 REGEXP_SUBSTR
5. 案例 REGEXP_REPLACE
五、复杂案例
1， REGEXP_SUBSTR 为指定字符串的一部分与正则表达式建立匹配。
示例1：
SQL> select regexp_substr('The zip code 80831 is for falcon, co','[[:digit:]]{5}' ) REGEXP_SUBSTR from dual;
REGEXP_SUBSTR
-------------
80831
示例2：
SQL> select regexp_substr('The zip code 80831 is for falcon, co', '[[:alpha:]]{3,}', 1, 3) REGEXP_SUBSTR from dual;
REGEXP_SUBSTR
-------------
code
示例3
SQL> select regexp_substr('comments or questions - email feedback@plsqlbook.com', '[[:alnum:]](([_\.\-\+]?[[:alnum:]]+)*)@'
|| '([[:alnum:]]+)(([\.-]?[[:alnum:]]+)*)\.([[:alpha:]]{2,})') REGEXP_SUBSTR from dual; REGEXP_SUBSTR ---------------------- feedback@plsqlbook.com
2. REGEXP_INSTR 返回与正则表达式匹配的字符和字符串的位置。
SQL> select regexp_instr('The zip code 80831 is for falcon, co', '[[:digit:]]{5}') REGEXP_INSTR from dual;
REGEXP_INSTR
------------
14
3. REGEXP_REPLACE 与REPLACE函数类似，提供一种修改与所给正则表达式匹配的字符串的方法。作用包括纠正拼写错误、格式化输入输出的文本。
如电话号码的格式为：719-111-1111。使用REGEX_REPLACER的返回值是：
SQL> select regexp_replace('Reformat the phone number 719-111-1111 ...', '[1]?[-.]?(\(?[[:digit:]]{3}\)?)+[- .]?'
|| '([[:digit:]]{3})[- .]?([[:digit:]]{4})', ' (\1) \2-\3') regexp_replace from dual; REGEXP_REPLACE --------------------------------------------- Reformat the phone number (719) 111-1111 ...
4. REGEXP_LIKE 运算符与LIKE运算符相似，但是功能更强大，因为它支持使用与此正则表达式与文本进行匹配。
SQL> select ename, job from emp where regexp_like(job, '(clerk|analyst)', 'i');
ENAME JOB
---------- ---------
SMITH CLERK
SCOTT ANALYST
ADAMS CLERK
JAMES CLERK
FORD ANALYST
MILLER CLERK
Thanks and Regards
参考： http://blog.sina.com.cn/s/blog_56cacf83010005aw.html
技术交流，技术讨论，欢迎加入
Technology Blog Created By Oracle ERP - 鲍新建
