oracle中Regex的研究

oracle的Regex(regular expression)

Oracle 10g支援Regex的四個新函數分別是：REGEXP_LIKE、REGEXP_INSTR、REGEXP_SUBSTR、和REGEXP_REPLACE。

它們在用法上與Oracle SQL 函數LIKE、INSTR、SUBSTR 和REPLACE 用法，但是它們使用POSIX Regex代替了老的百分比符號（%）和萬用字元（_）字元。

 

POSIX Regex由標準的元字元（metacharacters）所構成：

'^' 匹配輸入字串的開始位置，在方括號運算式中使用，此時它表示不接受該字元集合。

'$' 匹配輸入字串的結尾位置。如果設定了 RegExp 對象的 Multiline 屬性，則 $ 也匹配 'n' 或 'r'。

'.' 匹配除分行符號 n之外的任何單字元。

'?' 匹配前面的子運算式零次或一次。

'+' 匹配前面的子運算式一次或多次。

'*' 匹配前面的子運算式零次或多次。

'|' 指明兩項之間的一個選擇。例子'^([a-z]+|[0-9]+)$'表示所有小寫字母或數字組合成的字串。

'( )' 標記一個子運算式的開始和結束位置。

'[]' 標記一個中括號運算式。

'{m,n}' 一個精確地出現次數範圍，m=<出現次數<=n，'{m}'表示出現m次，'{m,}'表示至少出現m次。

num 匹配 num，其中 num 是一個正整數。對所擷取的匹配的引用。

 

 

字元簇：

[[:alpha:]] 任何字母。

[[:digit:]] 任何數字。

[[:alnum:]] 任何字母和數字。

[[:space:]] 任何白字元。

[[:upper:]] 任何大寫字母。

[[:lower:]] 任何小寫字母。

[[:punct:]] 任何標點符號。

[[:xdigit:]] 任何16進位的數字，相當於[0-9a-fA-F]。

 

各種操作符的運算優先順序

轉義符

(), (?:), (?=), [] 圓括弧和方括弧

*, +, ?, {n}, {n,}, {n,m} 限定符

^, $, anymetacharacter 位置和順序

| “或”操作

 

 

REGEXP_LIKE 與LIKE 操作符相似。如果第一個參數匹配Regex它就解析為TRUE。例如WHERE REGEXP_LIKE(ENAME,'^J[AO]','i') 將在ENAME 以JA 或JO 開始的情況下返回一行資料。'I' 參數指定Regex是大小寫敏感的。另外還可以在CHECK 條件約束和函數索引中指定REGEXP_LIKE。例如：

ALTER TABLE EMP ADD CONSTRAINT REGEX01

CHECK (REGEXP_LIKE(ENAME,'^[[:alpha:]]+$'));

這條語句使得ENAME 欄位只能包含字母和數字字元（也就是說沒有空格或者標點符號）。試圖插入或者更新這些資料將導致一個ORA-2290 異常，或者檢查約束的有效性。

REGEXP_INSTR 與INSTR 函數類似。它返回一個字串中匹配一個Regex的第一個子串的開始位置。例如：

SELECT REGEXP_INSTR('The total is $400 for your purchase.','$[[:digit:]]+')

FROM DUAL;

這個查詢返回14，即$400在字串的開始位置。另外還可以指定子串出現的次數；開始搜尋的位置；是返回匹配的位置還是返回匹配之後字元的位置。

REGEXP_SUBSTR 返回匹配一個Regex的子串。雖然結合使用SUBSTR 和REGEXP_INSTR 及LENGTH 也可以實現這一功能，但是使用這個函數卻更為簡單。

SELECT REGEXP_INSTR('one,two,three','[^,]*') FROM DUAL;

這個查詢返回'one'，將第一個參數看成一個逗號分隔的列表並返回第一個逗號之前的所有字元。

REGEXP_REPLACE 返回初始參數被匹配子串替換之後的結果。例如：

SELECT REGEXP_REPLACE('The temperature is 23°F',

'([[:digit:]])+°F',

('1'-32)*5/9||'°C')

FROM DUAL;

這個查詢將尋找一個華氏溫度並將其轉換為攝氏度。它將返回：'The temperature is -5°C'。