oracle regular expression

     方括弧表達示

方括號運算式	描述
[[:alnum:]]	字母和數字混合的字元
[[:alpha:]]	字母字元
[[:cntrl:]]	控制字元
[[:digit:]]	數字字元
[[:graph:]]	映像字元
[[:lower:]]	小寫字母字元
[[:print:]]	列印字元
[[:punct：]]	標點符號字元
[[:space:]]	空白字元
[[:upper:]]	大寫字母字元
[[:xdigit:]]	十六進位數字字元

 為了便於記憶：分別做下英文的解釋：

alnum is short for alphabet and number

alpha is short for alphabet

cntrl is short for control

punct is short for punctuation

xdigit is short for hex and digit

 

元字元

 

元字元	描述
*？	如果有前面字串的0次以上出現時匹配
+？	如果有前面字串的1次以上出現時匹配
{m}	m是整數。它文本中找出給定子運算式的恰好m次出現
{n}?	前面的字串只出現一次時匹配
{m,}	M是整數。它在文本中找出給定子表達示的至少m次出現
{n,}?	匹配前面的字串至少n次
{m,n}	M和n是整數。它在文本中找出給定子表達示的m到n次出現
{n,m}	匹配前面的字串至少到n次，但不多於m次
c	查詢操作區分大小寫
i	查詢操作不區分大水寫
m	多行字串上的查詢，在源字串包含多行時，該查詢允許用（^）模式比對字元串的開始
n	通常匹配單個字元，也可以匹配新行
x	需要忽略正則表達示中的空白字元時，使用參數‘X’
\A	匹配字串首，而不是行首，因而多行字串不能匹配每一行
\d	匹配任一數字字元
\D	匹配任意非數字字元
\s	匹配任意空白字元
\S	匹配任意非空白字元
\w	匹配任一字元和數字。該字元和[:alnum:]之間的不同是\w包括底線
\W	匹配任意非Null 字元串
*	萬用字元。找出在文本中包含0或多次給定子運算式出現的記錄
+	找出在文本中包含1或多次給定子運算式出現的記錄
？	找出在文本中包含0次或1次給定子表達示出現的記錄
.	匹配文本中的任一字元
^	錨。如果該字元後的運算式出現在行首，則匹配成功
$	錨。如果該字元後的運算式出現在行首，則匹配成功
|	分隔字元，使用方法和OR相同
(….)	分組子運算式

 

Regex運算子和函數

 

a)     REGEXP_SUBSTR

REGEXP_SUBSTR為指定字串的一部分與Regex建立匹配。文法如下：

REGEXP_SUBSTR(source_string,

pattern,

start_position,

occurrence,

match_parameter)

其中source_string是必須的。可以是帶引號的字串或者變數。Pattern是用單引號引用的與Regex。Start_position指定了在字串中的準確位置，預設值為1。Occurrence是一個選項，指定在源字串匹配過程中相對其他字串，哪個字串應該匹配。最後，match_parameter也是一個選項，指定在匹配時是否區分大水寫。

樣本1：

SQL> select regexp_substr('The zip code 80831 is for falcon, co','[[:digit:]]{5}' ) REGEXP_SUBSTR from dual;

 

REGEXP_SUBSTR

-------------

80831

 

樣本2：

SQL> select regexp_substr('The zip code 80831 is for falcon, co', '[[:alpha:]]{3,}', 1, 3) REGEXP_SUBSTR from dual;

 

REGEXP_SUBSTR

-------------

code

 

樣本3

 

SQL> select regexp_substr('comments or questions - email feedback@plsqlbook.com', '[[:alnum:]](([_\.\-\+]?[[:alnum:]]+)*)@' ||

 2        '([[:alnum:]]+)(([\.-]?[[:alnum:]]+)*)\.([[:alpha:]]{2,})') REGEXP_SUBSTR

 3   from dual;

 

REGEXP_SUBSTR

----------------------

feedback@plsqlbook.com

 

 

b)     REGEXP_INSTR

REGEXP_INSTR返回與Regex匹配的字元和字串的位置。如

 

SQL> select regexp_instr('The zip code 80831 is for falcon, co', '[[:digit:]]{5}') REGEXP_INSTR from dual;

 

REGEXP_INSTR

------------

         14

 

c)     REGEXP_REPLACE

REGEXP_REPLACE與REPLACE函數類似，提供一種修改與所給Regex匹配的字串的方法。作用包括糾正拼字錯誤、格式化輸入輸出的文本。

如電話號碼的格式為：719-111-1111。使用REGEX_REPLACER的傳回值是：

SQL> select regexp_replace('Reformat the phone number 719-111-1111 ...',

 2        '[1]?[-.]?(\(?[[:digit:]]{3}\)?)+[- .]?'

 3        || '([[:digit:]]{3})[- .]?([[:digit:]]{4})',

 4        ' (\1) \2-\3') regexp_replace

 5   from dual;

 

REGEXP_REPLACE

---------------------------------------------

Reformat the phone number (719) 111-1111 ...

S

d)     REGEXP_LIKE

REGEXP_LIKE運算子與LIKE運算子相似，但是功能更強大，因為它支援使用與此Regex與文本進行匹配。文法如下：

REGEXP_LIKE(source_string, pattern, match_parameter)

Source_string可以是文字字串，如果前面例中的字串，也可以是包含某些字串的變數或列。Pattern是要進行匹配的Regex。Match_parameter用於指定在匹配時是否區分大小寫。

SQL> select ename, job

 2   from emp

 3  where regexp_like(job, '(clerk|analyst)', 'i');

 

ENAME     JOB

---------- ---------

SMITH     CLERK

SCOTT     ANALYST

ADAMS     CLERK

JAMES     CLERK

FORD      ANALYST

MILLER    CLERK