ORACLE正規表示式函式詳解--轉載整理

Oracle 10g提供了四個正規表示式函式regexp function: 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：字串存在換行的時候被作為多行處理 

##################################################################################

正規表示式由以下標準的元字元（metacharacters）所構成：

字元

[[:alpha:]]任何字母。[A-Za-z]

[[:digit:]]任何數字。[0-9]
[[:alnum:]]任何字母和數字。[A-Za-z0-9]
[:blank:]   [ ]，標準是空格和TAB，但Oracle裡只有空格！
[:cntrl:]   CONTROL鍵
[:graph:]  即所有可見字元,不包括空格
[:print:]  可列印字元 ,即空格＋所有可見字元
[[:space:]] 空白字元。
[[:punct:]] 任何標點符號。
[[:upper:]] 任何大寫字母。[A-Z]
[[:lower:]] 任何小寫字母。[a-z]
[[:xdigit:]]任何16進位制的數字，相當於[0-9a-fA-F]。
#######
[:class:]   字元類。class如alnum、digit等。
[.element.] 整理類。受NLS_SORT影響。將多位元組字元當作單位元組字元看待。如在西班牙語中ch、ll都是用多位元組表示的單位元組。
[=char=]    等同類。在當前語言（Locale）下，匹配所有的屬於相同的POSIX等同類字元指定的字元。如在西班牙語中，[[=n=]]指定了等同類字元n，它可以匹配El Ni?o中的N和 ?.
###########################################################################

邊界符集合

^   每一行的開頭，單行模式下等價於字串的開頭
$   每一行的結尾，單行模式下等價於字串的結尾
在單行模式下，^和\A等價，$和\Z等價。
##########################################################################

重複次數集合

*   匹配零次或多次--儘可能多的次數
?   零次或一次
+   一次或多次
{m} {m}?正好m次，貪婪與非貪婪一樣的
{m,}    {m,}?至少m次
{m, n}  {m, n}?最少m最多n次
在上述字元後加？，如：*?，即為非貪婪模式。
貪婪模式會獲取儘可能多的字元，而非貪婪模式會獲取儘可能少的字元
###############################################################

組合操作符

[…]     方括號內任意字元或字符集合中的一個。在其中所有的操作符號都被視為普通符號，但以下除外：
★範圍操作符：- 但當"-"位於字元列表的第一個或最後一個字元時，或位於範圍的結尾時，會被當作"-"本身來看待。
當右方括號"]"出現在列表的第一個位置時，它會被當作"]"本身來處理。
[^…]    方括號內^為第一個字元時，表示與其後所有字元都不匹配的字元
(…)     圓括號，將複雜表示式當作單一表示式來處理
..|..    或
abc      和。直接將字元連在一起寫
優先順序比較：圓括號>重複次數操作符>和>或。
例如，(f|ht)tps?: 表示
ftp:
ftps:
http:
https:
######################################################################

匹配操作符

\n      即後向引用。n為1~9，標識由圓括號裡取得的匹配字串。方向是從左到右。
轉義操作符
\       將其後緊跟著的操作字元當作普通字元看待。
例如 abc*def 可以匹配 abdef或abcccdef等，但無法匹配abc*def，後者需要abc\*def才能匹配
##################################################

一條正規表示式匹配函式示例解析：

SELECT * FORM tKHXX where  REGEXP_LIKE(SJHM,  '^[1]{1}[35]{1}[[:digit:]]{9}$' )
這個語句是從表中查手機號。
^ 表示開始
$ 表示結束
[]內部為匹配範圍
{}裡的內容表時個數
手機號碼的特點是以 1開頭接著是3或5再加9位的數字 所以這麼理解
1開頭 表示式為 ^[1]{1} 意為 開始1位裡包含1
3或5 表示式為 [35]{1}，即第二位包含有3或5的
9位數字結束 為: [[:digit:]]{9}$ 這裡[:digit:]為特殊寫法，代表為數字，再加個結束符$。即最後9位是數字。

#######################################################################

後向引用（backreference）：

後向引用是一個很有用的特性。它能夠把子表示式的匹配部分儲存在臨時緩衝區中，供以後重用。緩衝區從左至右進行編號，並利用 \digit 符號進行訪問。子表示式用一組圓括號來顯示。利用後向引用可以實現較複雜的替換功能。 

下面的regexp_replace('Steven Chen', '(.*) (.*)', '\2, \1')  此函式中，首先在正規表示式函式中，('Steven Chen', '(.*) (.*)', 其中兩個圓括號中會匹配出Chen, Steven；再使用"\數字“的符號進行引用。
    SQL> select regexp_replace('Steven Chen', '(.*) (.*)', '\2, \1') as reversed_name from dual;        
    REVERSED_NAME  
    --------------------  
    Chen, Steven 
在DDL中也可以正規表示式，比如Constraint, index, view
Sql程式碼  
    SQL> alter table person add constraint constraint_zip check (regexp_like(zip, '^[[:digit:]]+$'));  
      
    SQL> create index person_idx on person(regexp_substr(last_name, '^[[:upper:]]'));