From:http://blog.sina.com.cn/s/blog_69e7b8d701012tuj.html
在oracle数据库当中有字符处理的函数，比如substr()、replace()和instr()，当然也有做字符匹配的操作符like，不过这些函数和操作符在处理一些复杂的字符串时，会显得有些力不从心。从oracle
10g开始，Oracle内建了符合IEEE
POSIX (Portable Operating System for
Unix)标准的正则表达式。当然oracle也支持perl的正则表达式规则。熟练使用正则表达式，可以写出简洁，强大的SQL语句。实际上，它们类似于已有的操作符，但现在增加了强大的模式匹配功能。被搜索的数据可以是简单的字符串或是存储在数据库字符列中的大量文本。正规表达式让您能够以一种您以前从未想过的方式来搜索、替换和验证数据，并提供高度的灵活性。同时，正则表达式对oracle的约束也给与了加强。
正规表达式
正规表达式由一个或多个字符型文字或元字符组成。在最简单的格式下，正规表达式仅由字符文字组成，如正规表达式cat。它被读作字母c，接着是字母a和t，这种模式匹配cat、location和catalog之类的字符串。元字符提供算法来确定Oracle如何处理组成一个正规表达式的字符。当您了解了各种元字符的含义时，您将体会到正规表达式用于查找和替换特定的文本数据是非常强大的。如果我们简单理解的话，正则表达式就是一种字符串的组成和表示方法。
在使用这个新功能之前，您需要了解一些元字符的含义。句号(.)匹配一个正规表达式中的任意字符(除了换行符)。例如，正规表达式a.b匹配的字符串中首先包含字母a，接着是其它任意单个字符(除了换行符)，再接着是字母b。字符串axb、xaybx和abba都与之匹配，因为在字符串中隐藏了这种模式。如果您想要精确地匹配以a开头和以b结尾的一条三个字母的字符串，则您必须对正规表达式进行定位。脱字符号(^)元字符指示一行的开始，而美元符号($)指示一行的结尾。因此，正规表达式^a.b$匹配字符串aab、abb或axb。将这种方式与LIKE操作符提供的类似的模式匹配a_b相比较，其中"_"是单字符通配符。
下表是正则表达式部分元字符的解释(符合POSIX标准)：
^
使表达式定位至一行的开头
$
使表达式定位至一行的末尾
*
匹配0次或更多次
?
匹配0次或1次
+
匹配1次或更多次
{ｍ}
正好匹配m次
{ｍ，}
至少匹配m次
{ｍ，ｎ}
至少匹配m次但不超过n次
[:alpha:]
字母字符
[:lower:]
小写字母字符
[:upper:]
大写字母字符
[:digit:]
数字
[:alnum:]
字母数字字符
[:space:]
空白字符(禁止打印)，如回车符、换行符、竖直制表符和换页符
[:punct:]
标点字符
(...)
将子表达式分组为一个替换单元、量词单元或后向引用单元
[...]
匹配列表中的字符
[^...]
匹配不在列表中的字符
Oracle提供了四个支持正则表达式的函数:
REGEXP_LIKE , REGEXP_REPLACE , REGEXP_INSTR ,
REGEXP_SUBSTR。
REGEXP_LIKE：比较一个字符串是否与正则表达式匹配
(srcstr, pattern [,
match_option])
REGEXP_INSTR：在字符串中查找正则表达式，并且返回匹配的位置
(srcstr, pattern [, position [, occurrence [,
return_option [,
match_option]]]])
REGEXP_SUBSTR：返回与正则表达式匹配的子字符串
(srcstr, pattern [, position [, occurrence [,
match_option]]])
REGEXP_REPLACE：搜索并且替换匹配的正则表达式
(srcstr, pattern [, replacestr [, position [,
occurrence [, match_option]]]])
其中各参数的含义为:
srcstr:被查找的字符数据，可以是列和绑定变量等
pattern:正则表达式。
occurrence:出现的次数。默认为1。
position:开始位置
return_option:默认值为0，返回该模式的起始位置；值为1则返回符合匹配条件的下一个字符的起始位置。
replacestr:用来替换匹配模式的字符串。
match_option:匹配方式选项。缺省为c。
c：case
sensitive
I：case
insensitive
n：(.)匹配任何字符(包括newline)
m：字符串存在换行的时候被作为多行处理
下面通过一些具体的例子来说明如何使用这四个函数。在测试当中，你就会逐步的体会到这些正则表达式的优势
首先创建测试表TEST，并加载测试数据：[email protected]>
create table test (c1 int , testcol varchar2(100));Table
[email protected]> insert into test
values(100,'10d6h2');1 row [email protected]>
insert into test values(110,'100025');1 row
[email protected]> insert into test
values(120,'gift');1 row [email protected]> insert
into test values(130,'01083697902');1 row
[email protected]> insert into test
values(140,'010-400-7591');1 row [email protected]>
insert into test values(150,'ab c de');1 row
[email protected]> insert into test
values(160,'abcde');1 row [email protected]> insert
into test values(170,'[email protected]'|| chr(10)
||'[email protected]');1 row [email protected]>
insert into test values(180,'Steven');1 row
[email protected]> insert into test
values(190,'bac');1 row [email protected]> insert
into test values(200,'Stephen');1 row
[email protected]> commit;Commit
[email protected]> col testcol for
[email protected]> select * from
test; C1
TESTCOL------------------------------------------------------------ 100
10d6h2 110
100025 120
gift 130
01083697902 140
010-400-7591 150
ab c
de 160
abcde 170
[email protected] [email protected] 180
Steven 190
bac 200
Stephen11 rows selected.
一、REGEXP_LIKE测试1.找到表中testcol列只包含数字的记录[email protected]>
select * from test where
regexp_like(testcol,'^[0-9]+$'); C1
TESTCOL----------
------------------------------------------------------------ 110
100025 130
01083697902
2.找到表中testcol列只包含6个数字的记录[email protected]>
select * from test where
regexp_like(testcol,'^[0-9]{6}$'); C1
TESTCOL----------
------------------------------------------------------------ 110
100025
3.当然上面2的语句也可以使用下面的写法[email protected]>
select * from test where
regexp_like(testcol,'^\d{6}$'); C1
TESTCOL----------
------------------------------------------------------------ 110
100025 4.找到表中testcol列包含以Ste开头，中间是v或者是ph，以en结尾的字符串记录[email protected]>
select * from test where
regexp_like(testcol,'^Ste(v|ph)en$'); C1
TESTCOL----------
------------------------------------------------------------ 180
Steven 200
Stephen
5.上面的语句默认是区分大小写的，如果要不区分大小写的话，可以考虑使用'i'[email protected]>
select * from test where regexp_like(testcol,'^ste(v|ph)en$');no
rows [email protected]> select * from test where
regexp_like(testcol,'^ste(v|ph)en$','i'); C1
TESTCOL----------
------------------------------------------------------------ 180
Steven 200
Stephen
6.找到表中testcol列只包含字母字符的记录[email protected]>
select * from test where
regexp_like(testcol,'^[[:alpha:]]+$'); C1
TESTCOL----------
------------------------------------------------------------ 120
gift 160
abcde 180
Steven 190
bac 200
Stephen
7.如果想要找到表test中的testcol列以liu开头，中间字符任意，组后以cn结束，直接写语句是不行的，需要用到'm',视记录为多行[email protected]>
select * from test where regexp_like(testcol,'^liu.*cn$');no rows
[email protected]> select * from test where
regexp_like(testcol,'^liu.*cn$','m'); C1
TESTCOL----------
------------------------------------------------------------ 170
[email protected] [email protected]二、REGEXP_INSTR测试1.返回表中testcol列第一个非数字字符出现的位置[email protected]>
col testcol for [email protected]> set linesize
[email protected]> select
c1,testcol,regexp_instr(testcol,'\D') from
test; C1
TESTCOL REGEXP_INSTR(TESTCOL,'\D')----------
------------------------------
-------------------------- 100
10d6h2 3 110
100025 0 120
gift 1 130
01083697902 0 140
010-400-7591 4 150
ab c
de 1 160
abcde 1 170
[email protected] 1 [email protected] 180
Steven 1 190
bac 1 200
Stephen 111
rows selected.
2.返回表中testcol列从第一个字符开始，非数字字符第二次出现的位置[email protected]>
select c1,testcol,regexp_instr(testcol,'\D',1,2) from
test; C1
TESTCOL REGEXP_INSTR(TESTCOL,'\D',1,2)----------
------------------------------
------------------------------ 100
10d6h2 5 110
100025 0 120
gift 2 130
01083697902 0 140
010-400-7591 8 150
ab c
de 2 160
abcde 2 170
[email protected] 2 [email protected] 180
Steven 2 190
bac 2 200
Stephen 211
rows
selected.三、REGEXP_SUBSTR测试1.取出C1=100的那条记录的testcol列中的第一个非数字字符[email protected]>
col tt for [email protected]> col testcol for
[email protected]> select
c1,testcol,regexp_substr(testcol,'\D') tt from test where
c1=100; C1
TESTCOL TT----------
--------------------
---------- 100
10d6h2 d
2.取出test表中testcol列的第一个非数字的字符，和1的语句类似，但使用了不同的通配符[email protected]>
select c1,testcol,regexp_substr(testcol,'[^[:digit:]]') tt from
test; C1
TESTCOL TT----------
--------------------
---------- 100
10d6h2 d 110
100025 120
gift g 130
01083697902 140
010-400-7591 - 150
ab c
de a 160
abcde a 170
[email protected] t [email protected] 180
Steven S 190
bac b 200
Stephen S11
rows
selected.四、REGEXP_REPLACE测试1.替换C1=100的那条记录的testcol列中的非数字为[email protected]>
select * from test where
c1=100; C1
TESTCOL----------
-------------------- 100
[email protected]> update test set
testcol=regexp_replace(testcol,'[^[:digit:]]',0) 2 where
c1=100;1 row [email protected]> commit;Commit
[email protected]> select * from test where
c1=100; C1
TESTCOL----------
-------------------- 100
100602
五、后向引用(backreference)：
后向引用是一个很有用的特性。它能够把子表达式的匹配部分保存在临时缓冲区中，供以后重用。缓冲区从左至右进行编号，并利用\n符号进行访问，n从1-9。子表达式用一组圆括号来显示。利用后向引用可以实现较复杂的替换功能。
[email protected]> select
regexp_replace('tianjie I am','(.*) (.*) (.*)','\2 \3 \1!') from
dual;
REGEXP_REPLAC
-------------
I am tianjie!
六、正则表达式在DDL语句当中的使用
在索引创建，添加约束的时候也可以使用正则表达式。
通过正则表达式，oracle对于约束也做了进一步的扩展，看一个regexp_like在check约束中的效果通过正则表达式，要求插入的数据必须是以3个数字开头，中间是"-",然后再是8位数字的格式[email protected]>
create table t(id number,tel varchar2(20));Table
[email protected]> alter table t add constraint
t_tel_c check(regexp_like(tel,'^\d{3}-\d{8}$'));Table
altered.第一条是满足要求的记录，可以插入[email protected]> insert into t
values(100,'010-82370510');1 row
created.第二条和第三条记录不符合要求，插入被拒绝[email protected]> insert into
t values(200,'13911700320');insert into t
values(200,'13911700320')*ERROR at line 1:ORA-02290: check
constraint (SCOTT.T_TEL_C) [email protected]>
insert into t values(200,'01089000329');insert into t
values(200,'01089000329')*ERROR at line 1:ORA-02290: check
constraint (SCOTT.T_TEL_C) violated
![oracle中正则表达式规则,Oracle中的正则表达式(及函数)详解[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)