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=)