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