| 使用正則表達(dá)式編寫更好的 SQL （續(xù)） 作者：Alice Rischert 回到第 1 部分 后向引用 正則表達(dá)式的一個(gè)有用的特性是能夠存儲(chǔ)子表達(dá)式供以后重用；這也被稱為后向引用（在表 10 中對其進(jìn)行了概述）。它允許復(fù)雜的替換功能，如在新的位置上交換模式或顯示重復(fù)出現(xiàn)的單詞或字母。子表達(dá)式的匹配部分保存在臨時(shí)緩沖區(qū)中。緩沖區(qū)從左至右進(jìn)行編號，并利用 \digit 符號進(jìn)行訪問，其中 digit 是 1 到 9 之間的一個(gè)數(shù)字，它匹配第 digit 個(gè)子表達(dá)式，子表達(dá)式用一組圓括號來顯示。 接下來的例子顯示了通過按編號引用各個(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 語句顯示了用圓括號括住的三個(gè)單獨(dú)的子表達(dá)式。每一個(gè)單獨(dú)的子表達(dá)式包含一個(gè)匹配元字符 (.)，并緊跟著 * 元字符，表示任何字符（除換行符之外）都必須匹配零次或更多次?？崭駥⒏鱾€(gè)子表達(dá)式分開，空格也必須匹配。圓括號創(chuàng)建獲取值的子表達(dá)式，并且可以用 \digit 來引用。第一個(gè)子表達(dá)式被賦值為 \1 ，第二個(gè) \2，以此類推。這些后向引用被用在這個(gè)函數(shù)的最后一個(gè)參數(shù) (\3, \1 \2) 中，這個(gè)函數(shù)有效地返回了替換子字符串，并按期望的格式來排列它們（包括逗號和空格）。表 11 詳細(xì)說明了該正則表達(dá)式的各個(gè)組成部分。 后向引用對替換、格式化和代替值非常有用，并且您可以用它們來查找相鄰出現(xiàn)的值。接下來的例子顯示了使用 REGEP_SUBSTR 函數(shù)來查找任意被空格隔開的重復(fù)出現(xiàn)的字母數(shù)字值。顯示的結(jié)果給出了識別重復(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ū)分大小寫、換行符的匹配和保留多行輸入。 正則表達(dá)式的實(shí)際應(yīng)用 您不僅可以在隊(duì)列中使用正則表達(dá)式，還可以在使用 SQL 操作符或函數(shù)的任何地方（比如說在 PL/SQL 語言中）使用正則表達(dá)式。您可以編寫利用正則表達(dá)式功能的觸發(fā)器，以驗(yàn)證、生成或提取值。 接下來的例子演示了您如何能夠在一次列檢查約束條件中應(yīng)用 REGEXP_LIKE 操作符來進(jìn)行數(shù)據(jù)驗(yàn)證。它在插入或更新時(shí)檢驗(yàn)正確的社會(huì)保險(xiǎn)號碼格式。如 123-45-6789 和 123456789 之類格式的社會(huì)保險(xiǎn)號碼對于這種列約束條件是可接受的值。有效的數(shù)據(jù)必須以三個(gè)數(shù)字開始，緊跟著一個(gè)連字符，再加兩個(gè)數(shù)字和一個(gè)連字符，最后又是四個(gè)數(shù)字。另一種表達(dá)式只允許 9 個(gè)連續(xù)的數(shù)字。豎線符號 (|) 將各個(gè)選項(xiàng)分開。 ALTER TABLE students ADD CONSTRAINT stud_ssn_ck CHECK (REGEXP_LIKE(ssn, ‘^([[:digit:]]{3}-[[:digit:]]{2}-[[:digit:]]{4}|[[:digit:]]{9})$‘)) 由 ^ 和 $ 指示的開頭或結(jié)尾的字符都是不可接受的。確保您的正則表達(dá)式?jīng)]有分成多行或包含任何不必要的空格，除非您希望格式如此并相應(yīng)地進(jìn)行匹配。表 12 說明了該正則表達(dá)式示例的各個(gè)組成部分。 將正則表達(dá)式與現(xiàn)有的功能進(jìn)行比較 正則表達(dá)式有幾個(gè)優(yōu)點(diǎn)優(yōu)于常見的 LIKE 操作符和 INSTR、SUBSTR 及 REPLACE 函數(shù)的。這些傳統(tǒng)的 SQL 函數(shù)不便于進(jìn)行模式匹配。只有 LIKE 操作符通過使用 % 和 _ 字符匹配，但 LIKE 不支持表達(dá)式的重復(fù)、復(fù)雜的更替、字符范圍、字符列表和 POSIX 字符類等等。此外，新的正則表達(dá)式函數(shù)允許檢測重復(fù)出現(xiàn)的單詞和模式交換。這里的例子為您提供了正則表達(dá)式領(lǐng)域的一個(gè)概覽，以及您如何能夠在您的應(yīng)用程序中使用它們。 實(shí)實(shí)在在地豐富您的工具包 因?yàn)檎齽t表達(dá)式有助于解決復(fù)雜的問題，所以它們是非常強(qiáng)大的。正則表達(dá)式的一些功能難于用傳統(tǒng)的 SQL 函數(shù)來仿效。當(dāng)您了解了這種稍顯神秘的語言的基礎(chǔ)構(gòu)建程序塊時(shí)，正則表達(dá)式將成為您的工具包的不可缺少的一部分（不僅在 SQL 環(huán)境下也在其它的編程語言環(huán)境下）。為了使您的各個(gè)模式正確，雖然嘗試和錯(cuò)誤有時(shí)是必須的，但正則表達(dá)式的簡潔和強(qiáng)大是不容置疑的。 Alice Rischert (ar280@yahoo.com) 是哥倫比亞大學(xué)計(jì)算機(jī)技術(shù)與應(yīng)用系的數(shù)據(jù)庫應(yīng)用程序開發(fā)和設(shè)計(jì)方向的主席。她編寫了 Oracle SQL 交互手冊 第 2 版 （Prentice Hall，2002）和即將推出的 Oracle SQL 示例 （Prentice Hall，2003）。Rischert 擁有超過 15 年的經(jīng)驗(yàn)在財(cái)富 100 強(qiáng)公司內(nèi)擔(dān)任數(shù)據(jù)庫設(shè)計(jì)師、DBA 和項(xiàng)目主管，并且她自從 Oracle version 5 起就一直使用 Oracle 產(chǎn)品。 表 1：定位元字符 | 元字符 | 說明 | | ^ | 使表達(dá)式定位至一行的開頭 | | $ | 使表達(dá)式定位至一行的末尾 | 表 2：量詞或重復(fù)操作符 | 量詞 | 說明 | | * | 匹配 0 次或更多次 | | 匹配 0 次或 1 次 | | + | 匹配 1 次或更多次 | | {m} | 正好匹配 m 次 | | {m,} | 至少匹配 m 次 | | {m, n} | 至少匹配 m 次但不超過 n 次 | 表 3：預(yù)定義的 POSIX 字符類 | 字符類 | 說明 | | [:alpha:] | 字母字符 | | [:lower:] | 小寫字母字符 | | [:upper:] | 大寫字母字符 | | [:digit:] | 數(shù)字 | | [:alnum:] | 字母數(shù)字字符 | | [:space:] | 空白字符（禁止打?。?，如回車符、換行符、豎直制表符和換頁符 | | [:punct:] | 標(biāo)點(diǎn)字符 | | [:cntrl:] | 控制字符（禁止打印） | | [:print:] | 可打印字符 | 表 4：表達(dá)式的替換匹配和分組 | 元字符 | 說明 | | | | 替換 | 分隔替換選項(xiàng)，通常與分組操作符 () 一起使用 | | ( ) | 分組 | 將子表達(dá)式分組為一個(gè)替換單元、量詞單元或后向引用單元（參見“后向引用”部分） | | [char] | 字符列表 | 表示一個(gè)字符列表；一個(gè)字符列表中的大多數(shù)元字符（除字符類、^ 和 - 元字符之外）被理解為文字 | 表 5：REGEXP_LIKE 操作符 | 語法 | 說明 | REGEXP_LIKE(source_string, pattern
[, match_parameter]) | source_string 支持字符數(shù)據(jù)類型（CHAR、VARCHAR2、CLOB、NCHAR、NVARCHAR2 和 NCLOB，但不包括 LONG）。pattern 參數(shù)是正則表達(dá)式的另一個(gè)名稱。match_parameter 允許可選的參數(shù)（如處理換行符、保留多行格式化以及提供對區(qū)分大小寫的控制）。 | 表 6：REGEXP_INSTR 函數(shù) | 語法 | 說明 | REGEXP_INSTR(source_string, pattern
[, start_position
[, occurrence
[, return_option
[, match_parameter]]]]) | 該函數(shù)查找 pattern ，并返回該模式的第一個(gè)位置。您可以隨意指定您想要開始搜索的 start_position。 occurrence 參數(shù)默認(rèn)為 1，除非您指定您要查找接下來出現(xiàn)的一個(gè)模式。return_option 的默認(rèn)值為 0，它返回該模式的起始位置；值為 1 則返回符合匹配條件的下一個(gè)字符的起始位置。 | 表 7： 5 位數(shù)字加 4 位郵政編碼表達(dá)式的說明 | 語法 | 說明 | | 必須匹配的空白 | | [:digit:] | POSIX 數(shù)字類 | | ] | 字符列表的結(jié)尾 | | {5} | 字符列表正好重復(fù)出現(xiàn) 5 次 | | ( | 子表達(dá)式的開頭 | | - | 一個(gè)文字連字符，因?yàn)樗皇且粋€(gè)字符列表內(nèi)的范圍元字符 | | [ | 字符列表的開頭 | | [:digit:] | POSIX [:digit:]類 | | [ | 字符列表的開頭 | | ] | 字符列表的結(jié)尾 | | {4} | 字符列表正好重復(fù)出現(xiàn) 4 次 | | ) | 結(jié)束圓括號，結(jié)束子表達(dá)式 | | 量詞匹配分組的子表達(dá)式 0 或 1 次，從而使得 4 位代碼可選 | | $ | 定位元字符，指示行尾 | 表 8：REGEXP_SUBSTR 函數(shù) | 語法 | 說明 | REGEXP_SUBSTR(source_string, pattern
[, position [, occurrence
[, match_parameter]]]) | REGEXP_SUBSTR 函數(shù)返回匹配模式的子字符串。 | 表 9： REGEXP_REPLACE 函數(shù) | 語法 | 說明 | REGEXP_REPLACE(source_string, pattern
[, replace_string [, position
[,occurrence, [match_parameter]]]]) | 該函數(shù)用一個(gè)指定的 replace_string 來替換匹配的模式，從而允許復(fù)雜的“搜索并替換”操作。 | 表 10：后向引用元字符 | 元字符 | 說明 | | \digit | 反斜線 | 緊跟著一個(gè) 1 到 9 之間的數(shù)字，反斜線匹配之前的用括號括起來的第 digit 個(gè)子表達(dá)式。
（注意：反斜線在正則表達(dá)式中有另一種意義，取決于上下文，它還可能表示 Escape 字符。 | 表 11：模式交換正則表達(dá)式的說明 | 正則表達(dá)式項(xiàng)目 | 說明 | | ( | 第一個(gè)子表達(dá)式的開頭 | | . | 匹配除換行符之外的任意單字符 | | * | 重復(fù)操作符，匹配之前的 . 元字符 0 到 n 次 | | ) | 第一個(gè)子表達(dá)式的結(jié)尾；匹配結(jié)果在 \1
中獲?。ㄔ谶@個(gè)例子中，結(jié)果為 Ellen。） | | 必須存在的空白 | | ( | 第二個(gè)子表達(dá)式的開頭 | | . | 匹配除換行符之外的任意單個(gè)字符 | | * | 重復(fù)操作符，匹配之前的 . 元字符 0 到 n 次 | | ) | 第二個(gè)子表達(dá)式的結(jié)尾；匹配結(jié)果在 \2
中獲?。ㄔ谶@個(gè)例子中，結(jié)果為 Hildi。） | | 空白 | | ( | 第三個(gè)子表達(dá)式的開頭 | | . | 匹配除換行符之外的任意單字符 | | * | 重復(fù)操作符，匹配之前的 . 元字符 0 到 n 次 | | ) | 第三個(gè)子表達(dá)式的結(jié)尾；匹配結(jié)果在 \3
中獲取（在這個(gè)例子中，結(jié)果為 Smith。） | 表 12：社會(huì)保險(xiǎn)號碼正則表達(dá)式的說明 | 正則表達(dá)式項(xiàng)目 | 說明 | | ^ | 行首字符（正則表達(dá)式在匹配之前不能有任何前導(dǎo)字符。） | | ( | 開始子表達(dá)式并列出用 | 元字符分開的可替換選項(xiàng) | | [ | 字符列表的開頭 | | [:digit:] | POSIX 數(shù)字類 | | ] | 字符列表的結(jié)尾 | | {3} | 字符列表正好重復(fù)出現(xiàn) 3 次 | | - | 連字符 | | [ | 字符列表的開頭 | | [:digit:] | POSIX 數(shù)字類 | | ] | 字符列表的結(jié)尾 | | {2} | 字符列表正好重復(fù)出現(xiàn) 2 次 | | - | 另一個(gè)連字符 | | [ | 字符列表的開頭 | | [:digit:] | POSIX 數(shù)字類 | | ] | 字符列表的結(jié)尾 | | {4} | 字符列表正好重復(fù)出現(xiàn) 4 次 | | | | 替換元字符；結(jié)束第一個(gè)選項(xiàng)并開始下一個(gè)替換表達(dá)式 | | [ | 字符列表的開頭 | | [:digit:] | POSIX 數(shù)字類 | | ] | 字符列表的結(jié)尾 | | {9} | 字符列表正好重復(fù)出現(xiàn) 9 次 | | ) | 結(jié)束圓括號，結(jié)束用于替換的子表達(dá)式組 | | $ | 定位元字符，指示行尾；沒有額外的字符能夠符合模式 | | 
