oracle正規表示式

轉載http://space.itpub.net/10768286/viewspace-448847

http://kt8668.iteye.com/blog/122001

[@more@]

Oracle 8 和Oracle 9i中缺乏靈活性的 正規表示式最終在Oracle 10g中得到了解決。Oracle 目前內建了符合POSIX 標準的正規表示式。

四個新的函式分別是：REGEXP_LIKE、REGEXP_INSTR、REGEXP_SUBSTR、和EGEXP_REPLACE。它們在用法上與 Oracle SQL 函式LIKE、INSTR、SUBSTR 和REPLACE 用法，但是它們使用POSIX 正規表示式代替了老的百分號（%）和萬用字元（_）字元。

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

'^' 表示字串的開始
'$' 表示字串的結束
'.' 表示任何字元
字元的範圍，比如說'[a-z]'，表示任何ASCII 小寫字母，與字元類"[[:lower:]]"" 等價
'?' 允許一個後繼字元匹配零次或一次
'+' 允許一個後繼字元匹配一次或多次
'*' 表示零次或多次
可以使用"{m,n}" 指定一個精確地出現範圍，其意思是"出現從m 次到n 次"；"{m}" 表示"正好m次"；而"{m,}" 表示"至少m次"。還可以使用圓括號組合字元的集合，使用"|"（豎線）表示可替換。例如，字串'^([a-z]+|[0-9]+)$'將匹配所有由小寫字母或數字組合成的字串。

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

Alter TABLE EMP ADD CONSTRAINT REGEX01
CHECK (REGEXP_LIKE(ENAME,'^[[:alpha:]]+$'));

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

REGEXP_INSTR 與INSTR 函式類似。它返回一個字串中匹配一個正規表示式的第一個子串的開始位置。例如：

Select REGEXP_INSTR('The total is $400 for your purchase.','$[[:digit:]]+')
FROM DUAL;

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

REGEXP_SUBSTR 返回匹配一個正規表示式的子串。雖然結合使用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'。

下面是其官方文件：
Writing Better SQL Using Regular Expressions

By Alice Rischert

10g 的正規表示式是處理文字資料的一個強有力的工具, 正規表示式在許多程式
語言和Unix下已經使用很久了.
oracle 透過在SQL函式或者Where子句中執行正規表示式, 如果你對正規表示式不熟悉，
這篇文章可以給你一個大概的介紹, 熟悉正規表示式的讀者可以瞭解怎麼在Oracle SQL語言中實現這個功能
什麼是正規表示式？
正規表示式由一個或多個字元和/或者字元元， 最簡單的形式由字元組成， 像正規表示式cat,
被解釋為字元c後面跟著a和t, 這樣的模式匹配字串有 cat, location 和catalog. 字元元為Oracle怎麼處理

正規表示式提供了運算規則。 如果, 你瞭解了各種字元元的意思, 你就明白正規表示式在處理獨立和代替文字

字元方面是多麼的強大.
資料有效性， 重複詞的辨認, 無關的空白檢測，或者分解多個正則組成的字串， 可以用來檢查電話號碼

的有效性， 郵政編碼，E-mail地址， 社保號碼，IP地址， 檔名和路徑， 等等。甚至可以用來模式定位,

如HTML的標籤，數字， 日期，或者任何模式匹配的字元或者代替他們的模式
在Oracle 10g中使用正規表示式

為了控制正規表示式可以使用Oracle中新的REGEXP_LIKE, REGEXP_INSTR, 和REG_EXP_REPLACE 函式， 你將會

看到這些函式怎樣增強了LIKE, INSTR, SUBSTR和REPLACE函式的功能. 實際上，和已經存在的函式呵操作相似

，只是提供了強大的字元匹配能力。
正規表示式簡單的例子
使用新功能之前， 要了解一些字元元的含義。句號(.)匹配正規表示式中的任一個字元(不包括新的一行). 例

如正規表示式a.b匹配一個包括a， 然後任意一個字元， 然後是b的字串， 如字串axb, xaybx, abba。 如

果要匹配以a開頭以b結尾的三個字元的字串, 必須使用^字元元指定開始行, 以$符號結束， 因此, 正規表達

式^a.b$匹配的字串如: aab, abb 或者axb, 就像LIKE 操作一樣 a_b一樣。
預設的, 單個字元或者字串列表再正規表示式中只匹配一次，如過要多次匹配，必須使用迴圈操作。 如果

你想匹配一個以a開頭， 以b結尾的字串， 正規表示式為：^a.*b$, *字元元重複.0次，1次或者多次。
表2列出了完全的重複操作。 如果在表示式重使用圓括號, 可以建立子表示式，被重複執行數次。 例如, 正

則表示式b(an)*a 可以匹配 ba, bana, banana, yourbananasplit, 等等
oracle正規表示式支援POSIX字元類，見表3。 那意味著你可以很精確的使用你想要的字元型別。 想象一下

為一個不是按照字母順序的紀錄寫一個LIKE條件的查詢， Where條件很容易變得非常複雜
POSIX字元類必須以方括號結束([]), 例如正規表示式[[]]匹配一個小寫字元, [[]]{5}匹配5

個連續的小寫字元
除了POSIX字元類以外， 還可以在字元列中放置單獨的字元， 例如正規表示式^ab[cd]ef$匹配字串abcef

和abdef, 字元c或者d必須被選中.
大部分字串列表中的字元元從字面上解釋, 除了字元"^"和"-". 正規表示式看起來很複雜是由於有些字元元

有多種意思， 這取決於上下文, "^"就是這樣的一個字元元. 如果"^"是字元列中的第一個字元，就表示對這個

字串取反，因此, [^[]]就是表示查詢不包含數字的模式, ^[[]]匹配以數字開頭的字串.

字元"-"表示一個範圍，正規表示式[a-m]匹配從a到m的字母, 但是如果字元"-"在開頭，就表示一個連字元，

如[-afg].
前面的一個例子介紹了用圓括號建立子表示式, 可以用豎線(|)進入迴圈交替. 例如, 正規表示式t(a|e|i)n

允許t與n中的三個字元可以任選一個, 匹配的字串例如tan, tin, 和Pakistan, 但不包括teen, mountain或

者tune. 作為可選擇的,
表示式t(a|e|i)n 也可以被理解為字元列t[aei]n. 表4列出了這些字元元。
REGEXP_LIKE 操作
下面的SQL查詢中Where從句顯示了怎麼使用REGEXP_LIKE函式， 在ZIP列中查詢滿足正規表示式

[^[]]的模式。 查詢返回ZIPCODE表中包含字元不全是數字的行:
Select zip
FROM zipcode
Where REGEXP_LIKE(zip, '[^[]]')
ZIP
-----
ab123
123xy
007ab
abcxy

這個例子中正規表示式只是由字元元組成, 更多明確的數字字元類用冒號和方括號標示. 第二組括號巢狀了一

個字元類列表，就像前面說的，這是個必不可少的， 你可以用POSIX字元類來建立字元列表
REGEXP_INSTR函式
這個函式返回模式的開始位置， 有點像INSTR函式, 語法見表6，兩個函式不同之處是REGECP_INSTR可以讓你指

定一個模式而不是特定的查詢字串，這樣就提供了強大的功能, 下面的例子使用REGEXP_INSTR返回字串

"Joe Smith, 10045 Berry Lane, San Joseph, CA 91234"中5個郵政編碼的開始位置，如果正規表示式寫成

[[]]{5}, 得到的將是門牌號而不是郵編, 因為10045是第一個以5個數字出現的模式， 因此必須用字元

元"$"表明是最後一個, 這個函式將返回郵編的開始位置而不管門牌號：
Select REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234',
'[[]]{5}$')
AS rx_instr
FROM dual
RX_INSTR
----------
45

寫一些複雜的模式
我們擴充套件一下前面郵政編碼的模式匹配， 包括一個可選的4個數字，正規表示式為:

[[]]{5}(-[[]]{4})?$. 如果你的源字串以5個數字的郵編程式碼或者5個數字的郵編+4個數字的

郵編組成, 這個正規表示式可以用來找到開始位置
Select REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234-1234',
' [[]]{5}(-[[]]{4})?$')
AS starts_at
FROM dual
STARTS_AT
---------- 44
這個例子中括號內的表示式(-[[]]{4}) 被重複0次或者1次， 由"?"迴圈操作控制。如果試圖用傳統的

SQL函式取得這樣的結果對SQL專家來說都是很困難的。 要想對正規表示式的各種變數有個瞭解， 參見表7.
REGEXP_SUBSTR 函式
和SUBSTR函式差不多, 分解出字串的一部分, 表8列出了這個函式的用法。 下面的例子中，返回匹配模式",

[^,]*,"的字串，正規表示式查詢逗號後面跟一個空格，後面跟著非","的字元或者字串, 最後以逗號結束

。
Select REGEXP_SUBSTR('first field, second field , third field',
', [^,]*,')
FROM dual
REGEXP_SUBSTR('FIR
------------------
, second field ,

REGEXP_REPLACE 函式
傳統的REPLACE函式, 用一個字串代替另一個， 假設你的資料裡面有很多的空白字元要用一個空白字元替換

， 用REPLACE函式的話， 必須指出替換多少個空白字元。 下面的例子中在Joe和Smith中間有3個空白,

REPLACE函式的引數指定用一個空白字元替換兩個空白字元, 這樣就把原來的一個沒有替換
Select REPLACE('Joe Smith',' ', ' ')
AS replace
FROM dual
REPLACE
---------
Joe Smith
REGEXP_RELPLACE 函式會好一些， 語法在表9中列出。 下面的例子用一個空格替換兩個或者多個空格。 ()子

表示式包含一個空格， 可以重複一次或者多次， 由{2,}指示.

Select REGEXP_REPLACE('Joe Smith',
'( ){2,}', ' ')
AS RX_REPLACE
FROM dual
RX_REPLACE
----------
Joe Smith

Backreference

正規表示式的一個很有用的特點是可以儲存子表示式以後使用， 被稱為Backreferencing(在表10中彙總). 允

許複雜的替換能力如調整一個模式到新的位置或者指示被代替的字元或者單詞的位置. 被匹配的子表示式儲存

在臨時緩衝區中， 緩衝區從左到右編號, 透過數字符號訪問。
下面的例子列出了把名字 ELLen Hildi Smith 變成Smith, Ellen Hildi.
Select REGEXP_REPLACE(
'Ellen Hildi Smith',
'(.*) (.*) (.*)', '3, 1 2')
FROM dual
REGEXP_REPLACE('EL
------------------
Smith, Ellen Hildi

這個SQL語句顯示了3個獨立的以圓括號表示的子表示式, 每個子表示式匹配任意一個字元元, 圓括號建立的子

表示式獲得的值以數字參考， 第一個子表示式被賦予1, 第二個被賦予2...等等. Backreference在最後一

個引數中使用， 有效的返回了替換字串的格式(包括逗號和空格). 表11列出了這種正規表示式的各個組成部

分
Backreference 替換， 格式化， 取值方面很有用的. 下面的例子用REGEXP_SUBSTR函式來查詢重複出現的以

空格分開的詞。 顯示的結果指出重複的子串。

Select REGEXP_SUBSTR(
'The final test is is the implementation',
'([[]]+)([[]]+)1') AS substr
FROM dual
SUBSTR
------
is is

匹配引數選項

你可能已經注意到了正規表示式操作和函式包含了一個可選的匹配引數. 這個引數可以控制大小寫, 匹配新一

行的字元， 合併多行輸入。

正規表示式的實踐應用
正規表示式不僅可以用在查詢中， 還可以用在任何可以使用SQL操作或者函式的地方，例如PL/SQL語言中. 可

以在觸發器中利用正規表示式的功能來檢查資料有效性， 產生， 或者提取數值。
下面的例子演示了怎麼用REGEXP_LIKE 操作再列中檢查資料約束的有效性. 在insert或者update的時候檢查社

保號碼的正確格式. 社保號的格式像123-45-6789, 123456789, 有效的數字必須以3個數字開頭, 跟著一個連字

符, 兩個數字， 一個連字元， 最後是4個數字。 交替出現的表示式只能出現9個數字， 豎線分開每個單獨的

選擇.
Alter TABLE students
ADD CONSTRAINT stud_ssn_ck CHECK
(REGEXP_LIKE(ssn,
'^([[]]{3}-[[]]{2}-[[]]{4}|[[]]{9})$'))
以字元開始或者結尾都是不允許的，這個由"^"和"$."可以看出。 必須注意正規表示式不能被分開成多行或者

有空格. 表12解釋了正規表示式單獨組成部分的例子。
參見10g的主頁/products/database/oracle10g/index.html

正規表示式和其他已經存在的函式的比較

正規表示式比一般的LIKE, INSTR, SUBSTR和REPLACE函式有很多好處， 傳統的SQL函式沒有方便的模式匹配功

能， 雖然透過使用"%"和"_", LIKE操作可以匹配字串， 但LIKE不能支援重複的表示式, 複雜的交替, 字元

範圍， 字元列, POSIX字元類等等. 此外， 新的正規表示式可以檢測重複詞和交換匹配模式的位置， 上面的

例子可以大概看出怎麼在應用中使用正規表示式

非常穩固的與你的工具包結合

正規表示式是很有用的， 可以幫助你解決複雜的問題。 一些正規表示式的功能用傳統的SQL函式很難表達出來

。 當你學會了這個秘密語言之後， 正規表示式將會成為你的工具包裡不可缺少的一部分， 不僅僅是SQL， 還

包括其他的程式語言，雖然有時候要經過反覆試驗你的模式才能正確， 正則式的功能是顯而易見的.