oracle10g數(shù)據(jù)庫(kù)中如何使用正則表達(dá)式

匹配零個(gè)或多個(gè)匹配項(xiàng)

用于指定選擇性匹配項(xiàng)的選擇性運(yùn)算符

匹配行的開(kāi)頭和結(jié)尾

用于匹配列表（匹配該列表中的任何表達(dá)式）的方括號(hào)表達(dá)式

如果脫字符位于方括號(hào)內(nèi)部，則對(duì)表達(dá)式取非。

精確匹配  m   次

至少匹配  m   次，但不超過(guò)  n   次

指定一個(gè)字符類(lèi)并匹配該類(lèi)中的任何字符

可以有四種不同的含義：(1) 表示其自身；(2) 引用下一個(gè)字符；(3) 引入一個(gè)運(yùn)算符；(4) 不執(zhí)行任何操作

匹配一個(gè)或多個(gè)匹配項(xiàng)

匹配零個(gè)或一個(gè)匹配項(xiàng)

匹配所支持字符集中的任何字符（NULL 除外）

對(duì)表達(dá)式進(jìn)行分組（視作一個(gè)子表達(dá)式）

向后引用表達(dá)式

指定等價(jià)類(lèi)

指定一個(gè)對(duì)照元素（如多字符元素）

檢查 REGEXP_LIKE 函數(shù)的語(yǔ)法：

  REGEXP_LIKE(srcstr, pattern [,match_option]) 

在此函數(shù)中：

srcstr：搜索值
pattern：正則表達(dá)式
match_option：用于更改默認(rèn)匹配的選項(xiàng)?？梢园韵乱粋€(gè)或多個(gè)值：

“c”：使用區(qū)分大小寫(xiě)的匹配（默認(rèn)值）
“i”：使用區(qū)分大小寫(xiě)的匹配
“n”：允許匹配任何字符的運(yùn)算符
“m”：將源字符串作為多行處理

要找到 PRODUCT_INFORMATION 表的 PRODUCT_NAME 列中名稱(chēng)包含 SSP/S、SSP/V、SSS/V 或 SSS/S 的所有產(chǎn)品，請(qǐng)執(zhí)行以下腳本：

@relike.sql 

relike.sql   腳本包含以下 SQL：

SELECT product_nameFROM oe.product_informationWHERE regexp_like (product_name, 'SS[PS]/[VS]'); 

 REGEXP_INSTR   函數(shù)返回字符串中給定模式的位置。檢查語(yǔ)法：

  REGEXP_INSTR(srcstr, pattern [, position [, occurrence[, return_option [, match_option]]]]) 

在該函數(shù)中：

position:搜索起始位置
occurrence：要搜索的匹配項(xiàng)
return_option：指示匹配項(xiàng)的開(kāi)頭或結(jié)尾位置
match_option：用于更改默認(rèn)匹配的選項(xiàng)?？梢园韵乱粋€(gè)或多個(gè)值：

“c”：使用區(qū)分大小寫(xiě)的匹配（默認(rèn)值）
“i”：使用區(qū)分大小寫(xiě)的匹配
“n”：允許匹配任何字符的運(yùn)算符
“m”：將源字符串作為多行處理

要搜索產(chǎn)品名稱(chēng)以確定第一個(gè)非字母字符（無(wú)論其是大寫(xiě)還是小寫(xiě)）的位置，請(qǐng)執(zhí)行以下腳本：

@reinstr.sql 

reinstr.sql   腳本包含以下 SQL：

COLUMN non_alpha FORMAT 9999999999
SELECT product_name, REGEXP_INSTR(product_name, '[^[:alpha:]]')non_alpha
FROM   oe.product_information ; 

請(qǐng)注意， [^[:<class>:]] 表示一個(gè)字符類(lèi)，并匹配該類(lèi)中的任何字符； [:alpha:] 匹配任何字母字符。在本示例中，您將通過(guò)使用 ^ 對(duì)該表達(dá)式取非。

執(zhí)行同一查詢(xún)，但這次使用已區(qū)分大小寫(xiě)的“i”。執(zhí)行以下腳本：

@multiport2.sql 

multiport2.sql   腳本包含以下 SQL：

SELECT regexp_substr(to_char(translated_name), '^[a-z]+', 1, 1, 'i')FROM   oe.product_descriptionsWHERE  language_id = 'PT'AND    translated_name like 'G%' ; 

 由于在遇到非英文字符時(shí)，返回的字符串被截?cái)?，因此結(jié)果仍不完整。這是因?yàn)榉秶?[a-z] 對(duì) NLS_LANGUAGE 比較敏感。因此，需要相應(yīng)地設(shè)置 NLS_LANGUAGE 參數(shù)，以返回完整結(jié)果。執(zhí)行以下查詢(xún)：

@multiport3.sql 

multiport3.sql   腳本包含以下 SQL：

ALTER SESSION SET NLS_LANGUAGE=PORTUGUESE;SELECT regexp_substr(to_char(translated_name), '^[a-z]+', 1, 1, 'i')FROM   oe.product_descriptionsWHERE  language_id = 'PT'AND    translated_name like 'G%' ; 

 最后一步是查看用英語(yǔ)和葡萄牙語(yǔ)表示的結(jié)果以確保已經(jīng)完成了翻譯。執(zhí)行以下腳本：

@multiport4.sql 

multiport4.sql   腳本包含以下 SQL：

SELECT REGEXP_SUBSTR(i.product_name, '^[a-z]+', 1, 1, 'i') || ' = '
|| regexp_substr(to_char(d.translated_name), '^[a-z]+', 1, 1, 'i')
FROM   oe.product_descriptions d, oe.product_information i
WHERE  d.language_id = 'PT'
AND    d.translated_name like 'G%'
AND    i.product_id = d.product_id ;ALTER SESSION SET NLS_LANGUAGE=AMERICAN; 

通過(guò)執(zhí)行以下腳本實(shí)施約束：

@chkemail2.sql 

chkemail2.sql   腳本包含以下 SQL：

ALTER TABLE customersADD CONSTRAINT cust_email_addrCHECK(REGEXP_LIKE(cust_email,'@'))NOVALIDATE ; 

 再次執(zhí)行  @chkemail.sql   測(cè)試該約束。

由于電子郵件地址不包含要求的符號(hào)，因此它違反了校驗(yàn)約束。NOVALIDATE 子句確保不檢查現(xiàn)有數(shù)據(jù)。

 通過(guò)執(zhí)行以下腳本刪除約束：

@chkemail3.sql 

chkemail3.sql   腳本包含以下 SQL：

ALTER TABLE customers DROP CONSTRAINT cust_email_addr ; 

 后向引用

 正則表達(dá)式的一個(gè)有用的特性是能夠存儲(chǔ)子表達(dá)式供以后重用；這也被稱(chēng)為后向引用（在表 10  中對(duì)其進(jìn)行了概述）。它允許復(fù)雜的替換功能，如在新的位置上交換模式或顯示重復(fù)出現(xiàn)的單詞或字母。子表達(dá)式的匹配部分保存在臨時(shí)緩沖區(qū)中。緩沖區(qū)從左至右進(jìn)行編號(hào)，并利用 
\digit   符號(hào)進(jìn)行訪問(wèn)，其中 digit 是 1 到 9 之間的一個(gè)數(shù)字，它匹配第 digit 個(gè)子表達(dá)式，子表達(dá)式用一組圓括號(hào)來(lái)顯示。

 接下來(lái)的例子顯示了通過(guò)按編號(hào)引用各個(gè)子表達(dá)式將姓名  Ellen Hildi Smith   轉(zhuǎn)變?yōu)?nbsp; Smith, Ellen Hildi 。

 SELECT REGEXP_REPLACE( 
'Ellen Hildi Smith', 
'(.*) (.*) (.*)', '\3, \1 \2') 
FROM dual 
REGEXP_REPLACE('EL 
------------------ 
Smith, Ellen Hildi

 該 SQL 語(yǔ)句顯示了用圓括號(hào)括住的三個(gè)單獨(dú)的子表達(dá)式。每一個(gè)單獨(dú)的子表達(dá)式包含一個(gè)匹配元字符 (. )，并緊跟著*   元字符，表示任何字符（除換行符之外）都必須匹配零次或更多次。空格將各個(gè)子表達(dá)式分開(kāi)，空格也必須匹配。圓括號(hào)創(chuàng)建獲取值的子表達(dá)式，并且可以用  \digit   來(lái)引用。第一個(gè)子表達(dá)式被賦值為  \1   ，第二個(gè)  \2 ，以此類(lèi)推。這些后向引用被用在這個(gè)函數(shù)的最后一個(gè)參數(shù) (\3, \1 \2 ) 中，這個(gè)函數(shù)有效地返回了替換子字符串，并按期望的格式來(lái)排列它們（包括逗號(hào)和空格）。表
11   詳細(xì)說(shuō)明了該正則表達(dá)式的各個(gè)組成部分。

 后向引用對(duì)替換、格式化和代替值非常有用，并且您可以用它們來(lái)查找相鄰出現(xiàn)的值。接下來(lái)的例子顯示了使用REGEP_SUBSTR   函數(shù)來(lái)查找任意被空格隔開(kāi)的重復(fù)出現(xiàn)的字母數(shù)字值。顯示的結(jié)果給出了識(shí)別重復(fù)出現(xiàn)的單詞 is 的子字符串。

 SELECT REGEXP_SUBSTR( 
'The final test is is the implementation', 
'([[:alnum:]]+)([[:space:]]+)\1') AS substr 
FROM dual 
SUBSTR 
------ 
is is

 匹配參數(shù)選項(xiàng)

 您可能已經(jīng)注意到了正則表達(dá)式操作符和函數(shù)包含一個(gè)可選的匹配參數(shù)。這個(gè)參數(shù)控制是否區(qū)分大小寫(xiě)、換行符的匹配和保留多行輸入。

 正則表達(dá)式的實(shí)際應(yīng)用

 您不僅可以在隊(duì)列中使用正則表達(dá)式，還可以在使用 SQL 操作符或函數(shù)的任何地方（比如說(shuō)在 PL/SQL 語(yǔ)言中）使用正則表達(dá)式。您可以編寫(xiě)利用正則表達(dá)式功能的觸發(fā)器，以驗(yàn)證、生成或提取值。

 接下來(lái)的例子演示了您如何能夠在一次列檢查約束條件中應(yīng)用  REGEXP_LIKE   操作符來(lái)進(jìn)行數(shù)據(jù)驗(yàn)證。它在插入或更新時(shí)檢驗(yàn)正確的社會(huì)保險(xiǎn)號(hào)碼格式。如 123-45-6789 和 123456789 之類(lèi)格式的社會(huì)保險(xiǎn)號(hào)碼對(duì)于這種列約束條件是可接受的值。有效的數(shù)據(jù)必須以三個(gè)數(shù)字開(kāi)始，緊跟著一個(gè)連字符，再加兩個(gè)數(shù)字和一個(gè)連字符，最后又是四個(gè)數(shù) 字。另一種表達(dá)式只允許 9 個(gè)連續(xù)的數(shù)字。豎線符號(hào) (| ) 將各個(gè)選項(xiàng)分開(kāi)。

ALTER TABLE students  ADD CONSTRAINT stud_ssn_ck CHECK  (REGEXP_LIKE(ssn,  '^([[:digit:]]{3}-[[:digit:]]{2}-[[:digit:]]{4}|[[:digit:]]{9})$')) 

 由  ^   和  $   指示的開(kāi)頭或結(jié)尾的字符都是不可接受的。確保您的正則表達(dá)式?jīng)]有分成多行或包含任何不必要的空格，除非您希望格式如此并相應(yīng)地進(jìn)行匹配。表
12   說(shuō)明了該正則表達(dá)式示例的各個(gè)組成部分。

 將正則表達(dá)式與現(xiàn)有的功能進(jìn)行比較

 正則表達(dá)式有幾個(gè)優(yōu)點(diǎn)優(yōu)于常見(jiàn)的  LIKE   操作符和INSTR、SUBSTR   及  REPLACE   函數(shù)的。這些傳統(tǒng)的 SQL 函數(shù)不便于進(jìn)行模式匹配。只有  LIKE   操作符通過(guò)使用  %   和_   字符匹配，但  LIKE   不支持表達(dá)式的重復(fù)、復(fù)雜的更替、字符范圍、字符列表和 POSIX 字符類(lèi)等等。此外，新的正則表達(dá)式函數(shù)允許檢測(cè)重復(fù)出現(xiàn)的單詞和模式交換。這里的例子為您提供了正則表達(dá)式領(lǐng)域的一個(gè)概覽，以及您如何能夠在您的應(yīng)用程序中使用它們。

 實(shí)實(shí)在在地豐富您的工具包

 因?yàn)檎齽t表達(dá)式有助于解決復(fù)雜的問(wèn)題，所以它們是非常強(qiáng)大的。正則表達(dá)式的一些功能難于用傳統(tǒng)的 SQL 函數(shù)來(lái)仿效。當(dāng)您了解了這種稍顯神秘的語(yǔ)言的基礎(chǔ)構(gòu)建程序塊時(shí)，正則表達(dá)式將成為您的工具包的不可缺少的一部分（不僅在 SQL 環(huán)境下也在其它的編程語(yǔ)言環(huán)境下）。為了使您的各個(gè)模式正確，雖然嘗試和錯(cuò)誤有時(shí)是必須的，但正則表達(dá)式的簡(jiǎn)潔和強(qiáng)大是不容置疑的。

 Alice Rischert   (ar280@yahoo.com ) 是哥倫比亞大學(xué)計(jì)算機(jī)技術(shù)與應(yīng)用系的數(shù)據(jù)庫(kù)應(yīng)用程序開(kāi)發(fā)和設(shè)計(jì)方向的主席。她編寫(xiě)了  Oracle
SQL 交互手冊(cè) 第 2 版    （Prentice Hall，2002）和即將推出的  Oracle
SQL 示例   （Prentice Hall，2003）。Rischert 擁有超過(guò) 15 年的經(jīng)驗(yàn)在財(cái)富 100 強(qiáng)公司內(nèi)擔(dān)任數(shù)據(jù)庫(kù)設(shè)計(jì)師、DBA 和項(xiàng)目主管，并且她自從 Oracle version 5 起就一直使用 Oracle 產(chǎn)品。

 表 1：定位元字符

 表 2：量詞或重復(fù)操作符

 表 3：預(yù)定義的 POSIX 字符類(lèi)

 表 4：表達(dá)式的替換匹配和分組

 表 5：REGEXP_LIKE   操作符

 表 6：REGEXP_INSTR   函數(shù)

 表 7： 5 位數(shù)字加 4 位郵政編碼表達(dá)式的說(shuō)明

 表 8：REGEXP_SUBSTR   函數(shù)

 表 9：  REGEXP_REPLACE    函數(shù)

 表 10：后向引用元字符

 表 11：模式交換正則表達(dá)式的說(shuō)明

 表 12：社會(huì)保險(xiǎn)號(hào)碼正則表達(dá)式的說(shuō)明