SQLRegex

當我們要進行一些簡單的糊塗查詢時用百分比符號(%),萬用字元(_)就可以了.其中%表達任意長度的字串,_表示任意的某一個字元.

比如select * from emp where ename like 's%' or ename like 's_';

但如果在一些複雜的查詢中關用這兩個符號sql語句就會非常複雜,而且也不一定能實現.從Oracle 10g開始引入了在其他程式語言中普通使用的Regex.

主要有regexp_like,regexp_replace,regexp_substr,regexp_instr四個Regex函數.

Regex中的元字元:

元字元	意思	例子
\	說明要匹配的字元是一個特殊字元、常量或者後者引用。（後引用重複上一次的匹配)	\n 匹配分行符號 \\ 匹配 \ \( 匹配 ( \) 匹配 )
^	匹配字串的開頭位置	^a匹配arwen.但不匹配barwen.
$	匹配字串的末尾位置	en$匹配arwen.但不匹配arwenb.
*	匹配前面的字元0次或多次	a*rwen可以匹配rwen或aaarwen.
+	匹配前面的字元1次或多次	a+rwen可以匹配arwen或aarwen.但不能匹配rwen.
?	匹配前面的字元0次或1次	a?rwen可以匹配arwen或rwen.但不能匹配aarwen.
{n}	匹配前面的字元恰好是n次，其中n是整數	ar{2}wen可以匹配arrwen.但不能匹配arwen或arrrwen.
{n,m}	匹配前面的字元至少是n次，最多是m次.如果寫成 {n,}表示最少匹配n次.沒有上限.	ar{1,2}wen可以匹配arwen,arrwen.但不匹配awen或arrrwen.
.	點號,匹配除null,換行以外的任意單個字元	arw.n.可以匹配arwen,arwin.但不能匹配arween或arwn.
(pattern)	括弧中pattern是一個子Regex，匹配指定pattern模式的一個子運算式。	其實括弧就像一般語言運算式中的括弧.有時多加些括弧可增強可讀性.另外的用處見下面關於\n的描述.
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:]可以匹配非Null 字元 [:lower:]可以匹配小寫字母a-z [:print:]與[:graph:]類似，不同之處在於[:print:]包括空白字元 [:punct:]可以匹配標點符號.,""等等 [:space:]可以匹配所有的Null 字元 [:upper:]可以匹配大寫字母A-Z [:xdigit:]可以匹配十六進位數字0-9、A-F、a-f
\n	這是對前一次匹配命中的一個後引用，其中n是一個正整數	arw(en)\1可以匹配arwenen.注意\1前面必須是個加括弧的子運算式.

1.regexp_like:

regexp_like(x,pattern[,match_option]),查看x是否與pattern相匹配，該函數還可以提供一個可選的參數match_option字串說明預設的匹配選項。match_option的取值如下：
‘c’   說明在進行匹配時區分大小寫（預設值）；
 'i'   說明在進行匹配時不區分大小寫；
 'n'   (.)點號能表示所有單個字元,包括換行(俺還不知道什麼地方有用到換行.只知道sql裡面可以用chr(10)表示換行.
 'm'   字串存在換行的時候當作多行處理.這樣$就可匹配每行的結尾.不然的話$只匹配字串最後的位置.

樣本:select * from emp where regexp_like(ename,'^a[a-z]*n$');可以尋找ename中以a開頭以n結尾的行.例如ename為arwen或arwin或anden.但Arwen不能被匹配.因為預設是區分大小寫.如果是select * from emp where regexp_like(ename,'^a[a-z]*n$','i')則可以尋找ename為Arwen的行記錄.

 

2.regexp_instr:

REGEXP_INSTR(x,pattern[,start[,occurrence[,return_option[, match_option]]]])用於在x中尋找pattern。返回pattern在x中出現的位置。匹配位置從1開始。可以參考字串函數 INSTR()，參數相關：
    'start'   開始尋找的位置；
    'occurrence'   說明應該返回第幾次出現pattern的位置；
    'return_option'   說明應該返回什麼整數。若該參數為0，則說明要返回的整數是x中的一個字元的位置；若該參數為非0的整數，則說明要返回的整數為x中出現在pattern之後   的字元的位置；
    'match_option'   修改預設的匹配設定.與regexp_like裡面的相同.

樣本:

DECLARE     

V_RESULT INTEGER ;    

BEGIN     

  SELECT  REGEXP_INSTR('hello world','o',1,1,0) INTO  V_RESULT    

FROM  DUAL;    

 DBMS_OUTPUT.PUT_LINE(V_RESULT); 

END;

結果為5.即字母o第一個次出現的位置

如果regexp_instr('hello world','o',1,1,n)其中n為除0之外的整數.比如1,3.則結果為6.表示第一次出現字母o的後面一個字元的位置.

如果regexp_instr('hello world','o',1,2,0)則結果為9.表示第二次出現字母o的位置.

3.regexp_replace:

REGEXP_REPLACE(x,pattern[,replace_string[,start[,occurrence[, match_option]]]])用於在x中尋找pattern，並將其替換為replae_string。可以參考字串函數 REPLACE()，參數同REGEXP_INSTR函數

樣本:

DECLARE     

V_RESULT varchar2(90);    

BEGIN     

  SELECT  REGEXP_REPLACE('hello world','o','x',1,1) INTO  V_RESULT    

FROM  DUAL;    

DBMS_OUTPUT.PUT_LINE(V_RESULT); 

END;

結果為hellx world.

如果REGEXP_REPLACE('hello world','o','x'),則結果為hellx wxrld.

如果 REGEXP_REPLACE('hello world','o','x',1,2)則結果為hello wxrld.

4.regexp_substr:

REGEXP_SUBSTR(x,pattern[,start[,occurrence[, match_option]]])用於在x中尋找pattern並返回。可以參考字串函數 SUBSTR()，參數同REGEXP_INSTR函數.

例如:

DECLARE        

 V_RESULT VARCHAR2(255);      

BEGIN       

  SELECT 
REGEXP_SUBSTR('hello world','l{2}') INTO  V_RESULT       

   FROM  DUAL;             

 DBMS_OUTPUT.PUT_LINE(V_RESULT);           

END ; 

結果為ll

查詢到匹配的字串才返回匹配的字元.沒查到就返回空.