|
使用正則表達式編寫更好的 SQL （續(xù)）
作者：Alice Rischert
回到第 1 部分
后向引用
正則表達式的一個有用的特性是能夠存儲子表達式供以后重用；這也被稱為后向引用（在表 10 中對其進行了概述）。它允許復雜的替換功能，如在新的位置上交換模式或顯示重復出現(xiàn)的單詞或字母。子表達式的匹配部分保存在臨時緩沖區(qū)中。緩沖區(qū)從左至右進行編號，并利用 \digit 符號進行訪問，其中 digit 是 1 到 9 之間的一個數(shù)字，它匹配第 digit 個子表達式，子表達式用一組圓括號來顯示。
接下來的例子顯示了通過按編號引用各個子表達式將姓名 Ellen Hildi Smith 轉變?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ù)字值。顯示的結果給出了識別重復出現(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ū)分大小寫、換行符的匹配和保留多行輸入。
正則表達式的實際應用
您不僅可以在隊列中使用正則表達式，還可以在使用 SQL 操作符或函數(shù)的任何地方（比如說在 PL/SQL 語言中）使用正則表達式。您可以編寫利用正則表達式功能的觸發(fā)器，以驗證、生成或提取值。
接下來的例子演示了您如何能夠在一次列檢查約束條件中應用 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})$‘))
由 ^ 和 $ 指示的開頭或結尾的字符都是不可接受的。確保您的正則表達式?jīng)]有分成多行或包含任何不必要的空格，除非您希望格式如此并相應地進行匹配。表 12 說明了該正則表達式示例的各個組成部分。
將正則表達式與現(xiàn)有的功能進行比較
正則表達式有幾個優(yōu)點優(yōu)于常見的 LIKE 操作符和 INSTR、SUBSTR 及 REPLACE 函數(shù)的。這些傳統(tǒng)的 SQL 函數(shù)不便于進行模式匹配。只有 LIKE 操作符通過使用 % 和 _ 字符匹配，但 LIKE 不支持表達式的重復、復雜的更替、字符范圍、字符列表和 POSIX 字符類等等。此外，新的正則表達式函數(shù)允許檢測重復出現(xiàn)的單詞和模式交換。這里的例子為您提供了正則表達式領域的一個概覽，以及您如何能夠在您的應用程序中使用它們。
實實在在地豐富您的工具包
因為正則表達式有助于解決復雜的問題，所以它們是非常強大的。正則表達式的一些功能難于用傳統(tǒng)的 SQL 函數(shù)來仿效。當您了解了這種稍顯神秘的語言的基礎構建程序塊時，正則表達式將成為您的工具包的不可缺少的一部分（不僅在 SQL 環(huán)境下也在其它的編程語言環(huán)境下）。為了使您的各個模式正確，雖然嘗試和錯誤有時是必須的，但正則表達式的簡潔和強大是不容置疑的。
Alice Rischert (ar280@yahoo.com) 是哥倫比亞大學計算機技術與應用系的數(shù)據(jù)庫應用程序開發(fā)和設計方向的主席。她編寫了 Oracle SQL 交互手冊 第 2 版 （Prentice Hall，2002）和即將推出的 Oracle SQL 示例 （Prentice Hall，2003）。Rischert 擁有超過 15 年的經(jīng)驗在財富 100 強公司內擔任數(shù)據(jù)庫設計師、DBA 和項目主管，并且她自從 Oracle version 5 起就一直使用 Oracle 產(chǎn)品。
表 1：定位元字符
| 元字符 |
說明 |
| ^ |
使表達式定位至一行的開頭 |
| $ |
使表達式定位至一行的末尾 |
| 量詞 |
說明 |
| * |
匹配 0 次或更多次 |
|
匹配 0 次或 1 次 |
| + |
匹配 1 次或更多次 |
| {m} |
正好匹配 m 次 |
| {m,} |
至少匹配 m 次 |
| {m, n} |
至少匹配 m 次但不超過 n 次 |
表 3：預定義的 POSIX 字符類
| 字符類 |
說明 |
| [:alpha:] |
字母字符 |
| [:lower:] |
小寫字母字符 |
| [:upper:] |
大寫字母字符 |
| [:digit:] |
數(shù)字 |
| [:alnum:] |
字母數(shù)字字符 |
| [:space:] |
空白字符（禁止打?。?，如回車符、換行符、豎直制表符和換頁符 |
| [:punct:] |
標點字符 |
| [:cntrl:] |
控制字符（禁止打?。?/span> |
| [: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ù)字類 |
| ] |
字符列表的結尾 |
| {5} |
字符列表正好重復出現(xiàn) 5 次 |
| ( |
子表達式的開頭 |
| - |
一個文字連字符，因為它不是一個字符列表內的范圍元字符 |
| [ |
字符列表的開頭 |
| [:digit:] |
POSIX [:digit:]類 |
| [ |
字符列表的開頭 |
| ] |
字符列表的結尾 |
| {4} |
字符列表正好重復出現(xiàn) 4 次 |
| ) |
結束圓括號，結束子表達式 |
|
量詞匹配分組的子表達式 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 次 |
| ) |
第一個子表達式的結尾；匹配結果在 \1
中獲?。ㄔ谶@個例子中，結果為 Ellen。） |
|
必須存在的空白 |
| ( |
第二個子表達式的開頭 |
| . |
匹配除換行符之外的任意單個字符 |
| * |
重復操作符，匹配之前的 . 元字符 0 到 n 次 |
| ) |
第二個子表達式的結尾；匹配結果在 \2
中獲?。ㄔ谶@個例子中，結果為 Hildi。） |
|
空白 |
| ( |
第三個子表達式的開頭 |
| . |
匹配除換行符之外的任意單字符 |
| * |
重復操作符，匹配之前的 . 元字符 0 到 n 次 |
| ) |
第三個子表達式的結尾；匹配結果在 \3
中獲取（在這個例子中，結果為 Smith。） |
表 12：社會保險號碼正則表達式的說明
| 正則表達式項目 |
說明 |
| ^ |
行首字符（正則表達式在匹配之前不能有任何前導字符。） |
| ( |
開始子表達式并列出用 | 元字符分開的可替換選項 |
| [ |
字符列表的開頭 |
| [:digit:] |
POSIX 數(shù)字類 |
| ] |
字符列表的結尾 |
| {3} |
字符列表正好重復出現(xiàn) 3 次 |
| - |
連字符 |
| [ |
字符列表的開頭 |
| [:digit:] |
POSIX 數(shù)字類 |
| ] |
字符列表的結尾 |
| {2} |
字符列表正好重復出現(xiàn) 2 次 |
| - |
另一個連字符 |
| [ |
字符列表的開頭 |
| [:digit:] |
POSIX 數(shù)字類 |
| ] |
字符列表的結尾 |
| {4} |
字符列表正好重復出現(xiàn) 4 次 |
| | |
替換元字符；結束第一個選項并開始下一個替換表達式 |
| [ |
字符列表的開頭 |
| [:digit:] |
POSIX 數(shù)字類 |
| ] |
字符列表的結尾 |
| {9} |
字符列表正好重復出現(xiàn) 9 次 |
| ) |
結束圓括號，結束用于替換的子表達式組 |
| $ |
定位元字符，指示行尾；沒有額外的字符能夠符合模式 |
|
