使用正則表達(dá)式編寫(xiě)更好的 SQL （續(xù)）

作者：Alice Rischert

回到第 1 部分

后向引用

正則表達(dá)式的一個(gè)有用的特性是能夠存儲(chǔ)子表達(dá)式供以后重用；這也被稱為后向引用（在表 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)?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，以此類推。這些后向引用被用在這個(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 之類格式的社會(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 字符類等等。此外，新的正則表達(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 字符類

表 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ō)明