文章介绍了关于oracle正则函数的一些用法，包括匹配等,oracle10g提供了在查询中使用正则表达的功能，它是通过各种支持正则表达式的函数在where子句中实现的
oracle中的支持正则表达式的函数主要有下面四个：
1，regexp_like ：与like的功能相似
2，regexp_instr ：与instr的功能相似
3，regexp_substr ：与substr的功能相似
4，regexp_replace ：与replace的功能相似
1、正则表达式中的元字符
元字符 意思 例子
说明要匹配的字符是一个特殊字符、常量或者后者引用。（后引用重复上一次的匹配） n 匹配换行符
\ 匹配
( 匹配 (
) 匹配 )
^ 匹配字符串的开头位置 如果a是字符串的第一个字符，^a 匹配 a
$ 匹配字符串的末尾位置 如果b是字符串的最后一个字符，$b 匹配 b
* 匹配前面的字符0次或多次 ba*rk可以匹配 brk、bark、baark等等
+ 匹配前面的字符1次或多次 ba+rk可以匹配 bark、baark等等,但是不能匹配brk，也就是说，最少有以一次。
? 匹配前面的字符0次或1次 ba?rk可以匹配 bark、brk等等,但是不能匹配baark。
{n} 匹配前面的字符恰好是n次，其中n是整数 hob{2}it可以匹配hobbit
{n,m} 匹配前面的字符至少是n次，最多是m次，其中n，m都是整数 hob{2,3}it可以匹配hobbit或者hobbbit
. 匹配除null以外的任意单个字符 hob.it中的.可以是任意的单个字符，如：hobsit等等
(pattern) 括号中pattern是一个子正则表达式，匹配指定pattern模式的一个子表达式。 如：aaa(x|y)可以匹配aaax或者aaay。
x|y 匹配“或” x|y可以匹配x或者y
[abc] 可以匹配abc中的任何单个字符 hello[abc]可以匹配helloa,hellob,helloc
[a-z] 可以匹配指定范围内的任何单个字符 hell[a-z]可以匹配hello或者hellz
[::] 指定一个字符类，可以匹配该类中的任何字符 [:alphanum:]可以匹配字符0-9、a-z、a-z
[:alpha:]可以匹配字符a-z、a-z
[:blank:]可以匹配空格或tab键
[:digit:]可以匹配数字0-9
[:graph:]可以匹配非空字符
[:lower:]可以匹配小写字母a-z
[:print:]与[:graph:]类似，不同之处在于[:print:]包括空格字符
[:punct:]可以匹配标点符号.,等等
[:space:]可以匹配所有的空字符
[:upper:]可以匹配大写字母a-z
[:xdigit:]可以匹配十六进制数字0-9、a-f、a-f
n 这是对前一次匹配命中的一个后引用，其中n是一个正整数 (.)1可以匹配两个连续相同的非空字符。(.)可以匹配除null以外的任何单个字符，而1则重复上一次匹配的内容，即再次匹配相同的字符，因此可以匹配两个连续相同的非空字符
2、regexp_like(x,pattern[,match_option])用于在x中查找正则表达式pattern，该函数还可以提供一个可选的参数match_option字符串说明默认的匹配选项。match_option的取值如下：
‘c’ 说明在进行匹配时区分大小写（缺省值）；
'i' 说明在进行匹配时不区分大小写；
'n' 允许使用可以匹配任意字符的操作符；
'm' 将x作为一个包含多行的字符串。
--测试数据
代码如下 复制代码
create table test(mc varchar2(60));
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;
一、regexp_like
代码如下 复制代码
select * from test where regexp_like(mc,'^a{1,3}');
select * from test where regexp_like(mc,'a{1,3}');
select * from test where regexp_like(mc,'^a.*e$');
select * from test where regexp_like(mc,'^[[:lower:]]|[[:digit:]]');
select * from test where regexp_like(mc,'^[[:lower:]]');
select mc from test where regexp_like(mc,'[^[:digit:]]');
select mc from test where regexp_like(mc,'^[^[:digit:]]');
二、regexp_instr
代码如下 复制代码
select regexp_instr(mc,'[[:digit:]]$') from test;
select regexp_instr(mc,'[[:digit:]]+$') from test;
select regexp_instr('the price is $400.','$[[:digit:]]+') from dual;
select regexp_instr('onetwothree','[^[[:lower:]]]') from dual;
select regexp_instr(',,,,,','[^,]*') from dual;
select regexp_instr(',,,,,','[^,]') from dual;
三、regexp_substr
代码如下 复制代码
select regexp_substr(mc,'[a-z]+') from test;
select regexp_substr(mc,'[0-9]+') from test;
select regexp_substr('aababcde','^a.*b') from dual;
四、regexp_replace
代码如下 复制代码
select regexp_replace('joe smith','( ){2,}', ',') as rx_replace from dual;
select regexp_replace('aa bb cc','(.*) (.*) (.*)', '3, 2, 1') from dual；
sql> select * from test;
id mc
-------------------- ------------------------------------------------------------
a aaaaa
a aaaaa
b bbbbb
sql> select * from test where regexp_like(id,'b','i'); --不区分数据大小写
id mc
-------------------- ------------------------------------------------------------
b bbbbb
#end
接下来的几节将会介绍更多有关正则表达式函数的知识。
1. regexp_like()
regexp_like(x, pattern [, match_option])用于在x中查找pattern参数中定义的正则表达式，该函数还可以提供一个可选参数match_option，它可以设置为下面几个字符之一：
'c'，说明在匹配时区分大小写(默认选项)
'i'，说明在匹配时不区分大小写
'n'，允许使用可以匹配任意字符的操作符
'm'，将x 作为一个包含多行的字符串
下面这个查询使用regexp_like函数检索生日在1965年到1968年之间的顾客：
代码如下 复制代码
select customer_id, first_name, last_name, dob
from customers
where regexp_like(to_char(dob, 'yyyy'), '^196[5-8]$');
customer_id first_name last_name dob
----------- ---------- ---------- ---------
1 john brown 01-jan-65
2 cynthia green 05-feb-68
下面这个查询检索名字以j或j开头的顾客。注意传递给regexp_like()的正则表达式是 ^j，匹配选项是i，这说明不区分大小写，因此在本例中，^j 可以匹配j或j：
代码如下 复制代码
select customer_id, first_name, last_name, dob
from customers
where regexp_like(first_name, '^j', 'i');
customer_id first_name last_name dob
----------- ---------- ---------- ---------
1 john brown 01-jan-65
2. regexp_instr()
regexp_instr(x, pattern [, start [, occurrence [, return_option [, match_option]]]])用于在x中查找pattern；regexp_instr()返回pattern出现的位置。匹配位置从1开始。
下面这个查询使用regexp_instr函数返回匹配正则表达式 l[[:alpha:]]{4}的位置：
代码如下 复制代码
select
regexp_instr('but, soft! what light through yonder window breaks?',
'l[[:alpha:]]{4}') as result
from dual;
result
----------
17
注意返回值为17，这是light中l的位置。
下面这个查询返回第二次匹配正则表达式 s[[:alpha:]]{3}的位置，匹配位置从1开始：
代码如下 复制代码
select
regexp_instr('but, soft! what light through yonder window softly breaks?',
's[[:alpha:]]{3}', 1, 2) as result
from dual;
result
----------
45
下面这个查询使用regexp_instr函数返回第二次匹配字母 o 的位置，匹配位置从10开始：
代码如下 复制代码
select
regexp_instr('but, soft! what light through yonder window breaks?',
'o', 10, 2) as result
from dual;
result
----------
32
3. regexp_replace()
regexp_replace(x, pattern [, replace_string [, start [, occurrence[, match_option]]]])用于在x中查找pattern，并将其替换为 replace_string。
下面这个查询使用regexp_replace函数将匹配正则表达式 l[[:alpha:]]{4}的子字符串替换为字符串 sound：
代码如下 复制代码
select
regexp_replace('but, soft! what light through yonder window breaks?',
'l[[:alpha:]]{4}', 'sound') as result
from dual;
result
---------------------------------------------------
but, soft! what sound through yonder window breaks?
注意light已经被替换为sound。
4. regexp_substr()
regexp_substr(x, pattern[, start [, occurrence[, match_option]]])用于在x中查找匹配pattern的子字符串，开始位置由 start指定。
下面这个查询使用regexp_substr函数返回匹配正则表达式 l[[:alpha:]]{4}的子字符串：
代码如下 复制代码
select
regexp_substr('but, soft! what light through yonder window breaks?',
'l[[:alpha:]]{4}') as result
from dual;
resul
-----
light
5. regexp_count()
regexp_count()是oracle database11g新增加的一个函数。regexp_count(x, pattern[, start [,match_option]])用于在x中查找pattern，并返回pattern在x中出现的次数。可以提供可选参数start，指出要从x中开始查找pattern的那个字符；也可以提供可选的match_option字符串，指出匹配选项。
下面这个查询使用regexp_count函数返回正则表达式s[[:alpha:]]{3}出现的次数：
代码如下 复制代码
select
regexp_count('but, soft! what light through yonder window softly breaks?',
's[[:alpha:]]{3}') as result
from dual;
result
----------
2
注意返回结果是2，这表明正则表达式在提供的字符串中有两次匹配。
•oracle正则表达匹配手机特号
尾号四连号：([0123456789])111$ 如：13498212222、13613431111
sql：select * from tb_phone where regexp_like(phone_no,'([0123456789])111$')
尾号四连顺：(0123|1234|2345|3456|4567|5678|6789)$ 如：13576531234、13623432345
尾号倒四连顺：(9876|8765|7654|6543|5432|4321|3210)$ 如：13512329876、13676987654
尾号00xx：00[[:digit:]][[:digit:]]$ 如：13512320023、13512320035
尾号aabb：([[:digit:]])1([[:digit:]])2$ 如：13567545566
尾号abab：([[:digit:]]{2})1$ 如：13545341212
尾号aaab:([[:digit:]])11[[:digit:]]$ 如：13564326667