使用正規表達式編寫更好的 SQL

在這個示例中，括弧裡的子表達式 (-[[:digit:]]{4}) 將按 ? 重復操作符的指示重復零次或一次。此外，企圖用傳統的 SQL 函數來實現相同的結果甚至對 SQL 專家也是一個挑戰。為了更好地說明這個正規表達式示例的不同組成部分，表 7 包含了一個對單個文字和元字符的描述。

REGEXP_SUBSTR 函數

·Ç³£ÀàËÆÓÚ SUBSTR 函數的 REGEXP_SUBSTR 函數用來提取一個字符串的一部分。表 8 顯示了這個新函數的語法。

SELECT REGEXP_SUBSTR(''first field, second field , third fIEld'',
'', [^,]*,'')
FROM dual
REGEXP_SUBSTR(''FIR
------------------
, second fIEld ,

REGEXP_REPLACE 函數

讓我們首先看一下傳統的 REPLACE SQL 函數，它把一個字符串用另一個字符串來替換。假設您的數據在正文中有不必要的空格，您希望用單個空格來替換它們。利用 REPLACE 函數，您需要准確地列出您要替換多少個空格。然而，多余空格的數目在正文的各處可能不是相同的。下面的示例在 Joe 和 Smith 之間有三個空格。REPLACE 函數的參數指定要用一個空格來替換兩個空格。在這種情況下，結果在原來的字符串的 Joe 和 Smith 之間留下了一個額外的空格。

SELECT REPLACE(''Joe Smith'','' '', '' '')
AS replace
FROM dual
REPLACE
---------
Joe Smith

REGEXP_REPLACE 函數把替換功能向前推進了一步，其語法在表 9 中列出。以下查詢用單個空格替換了任意兩個或更多的空格。( ) 子表達式包含了單個空格，它可以按 {2,} 的指示重復兩次或更多次。

SELECT REGEXP_REPLACE(''Joe Smith'',
''( ){2,}'', '' '')
AS RX_REPLACE
FROM dual
RX_REPLACE
----------
Joe Smith 

後向引用

正則表達式的一個有用的特性是能夠存儲子表達式供以後重用；這也被稱為後向引用（在

接下來的例子顯示了通過按編號引用各個子表達式將姓名 Ellen Hildi Smith 轉變為 Smith, Ellen Hildi。

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

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

後向引用對替換、格式化和代替值非常有用，並且您可以用它們來查找相鄰出現的值。接下來的例子顯示了使用 REGEP_SUBSTR 函數來查找任意被空格隔開的重復出現的字母數字值。顯示的結果給出了識別重復出現的單詞 is 的子字符串。

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

匹配參數選項

您可能已經注意到了正則表達式操作符和函數包含一個可選的匹配參數。這個參數控制是否區分大小寫、換行符的匹配和保留多行輸入。

正則表達式的實際應用

您不僅可以在隊列中使用正則表達式，還可以在使用 SQL 操作符或函數的任何地方（比如說在 PL/SQL 語言中）使用正則表達式。您可以編寫利用正則表達式功能的觸發器，以驗證、生成或提取值。

接下來的例子演示了您如何能夠在一次列檢查約束條件中應用 REGEXP_LIKE 操作符來進行數據驗證。它在插入或更新時檢驗正確的社會保險號碼格式。如 123-45-6789 和 123456789 之類格式的社會保險號碼對於這種列約束條件是可接受的值。有效的數據必須以三個數字開始，緊跟著一個連字符，再加兩個數字和一個連字符，最後又是四個數字。另一種表達式只允許 9 個連續的數字。豎線符號 (|) 將各個選項分開。

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

由 ^ 和 $ 指示的開頭或結尾的字符都是不可接受的。確保您的正則表達式沒有分成多行或包含任何不必要的空格，除非您希望格式如此並相應地進行匹配。表 12 說明了該正則表達式示例的各個組成部分。

訪問 Oracle Database 10g頁面：
/global/cn/products/database/Oracle10g/index.Html

將正則表達式與現有的功能進行比較

正則表達式有幾個優點優於常見的 LIKE 操作符和 INSTR、SUBSTR 及 REPLACE 函數的。這些傳統的 SQL 函數不便於進行模式匹配。只有 LIKE 操作符通過使用 % 和 _ 字符匹配，但 LIKE 不支持表達式的重復、復雜的更替、字符范圍、字符列表和 POSIX 字符類等等。此外，新的正則表達式函數允許檢測重復出現的單詞和模式交換。這裡的例子為您提供了正則表達式領域的一個概覽，以及您如何能夠在您的應用程序中使用它們。

實實在在地豐富您的工具包

因為正則表達式有助於解決復雜的問題，所以它們是非常強大的。正則表達式的一些功能難於用傳統的 SQL 函數來仿效。當您了解了這種稍顯神秘的語言的基礎構建程序塊時，正則表達式將成為您的工具包的不可缺少的一部分（不僅在 SQL 環境下也在其它的編程語言環境下）。

Alice Rischert ([email protected]) 是哥倫比亞大學計算機技術與應用系的數據庫應用程序開發和設計方向的主席。她編寫了 Oracle SQL 交互手冊 第 2 版 （Prentice Hall，2002）和即將推出的 Oracle SQL 示例 （Prentice Hall，2003）。Rischert 擁有超過 15 年的經驗在財富 100 強公司內擔任數據庫設計師、DBA 和項目主管，並且她自從 Oracle version 5 起就一直使用 Oracle 產品。

表 1：定位元字符 元字符 說明 ^ 使表達式定位至一行的開頭 $ 使表達式定位至一行的末尾

表 2：量詞或重復操作符 量詞 說明 * 匹配 0 次或更多次 ? 匹配 0 次或 1 次 + 匹配 1 次或更多次 {m} 正好匹配 m 次

表 3：預定義的 POSIX 字符類 字符類 說明 [:alpha:] 字母字符 [:lower:] 小寫字母字符 [:upper:] 大寫字母字符 [:digit:] 數字 [:alnum:] 字母數字字符 [:space:] 空白字符（禁止打印），如回車符、換行符、豎直制表符和換頁符 [:punct:] 標點字符 [:cntrl:] 控制字符（禁止打印） [:print:] 可打印字符

表 4：表達式的替換匹配和分組 元字符 說明 |

表 5：REGEXP_LIKE 操作符 語法 說明 REGEXP_LIKE(source_string, pattern
[, match_parameter]) source_string 支持字符數據類型（CHAR、VARCHAR2、CLOB、NCHAR、NVARCHAR2 和 NCLOB，但不包括 LONG）。pattern 參數是正則表達式的另一個名稱。match_parameter 允許可選的參數（如處理換行符、保留多行格式化以及提供對區分大小寫的控制）。

表 6：REGEXP_INSTR 函數 語法 說明 REGEXP_INSTR(source_string, pattern
[, start_position
[, occurrence
[, return_option
[, match_parameter]]]]) 該函數查找 n>pattern ，並返回該模式的第一個位置。您可以隨意指定您想要開始搜索的 start_position。 occurrence 參數默認為 1，除非您指定您要查找接下來出現的一個模式。return_option 的默認值為 0，它返回該模式的起始位置；值為 1 則返回符合匹配條件的下一個字符的起始位置。

表 7： 5 位數字加 4 位郵政編碼表達式的說明 語法 說明   必須匹配的空白 [:digit:] POSIX 數字類 ] 字符列表的結尾 {5} 字符列表正好重復出現 5 次 ( 子表達式的開頭 - 一個文字連字符，因為它不是一個字符列表內的范圍元字符 [ 字符列表的開頭 [:digit:] POSIX [:digit:]類 [ 字符列表的開頭 ] 字符列表的結尾 {4} 字符列表正好重復出現 4 次 ) 結束圓括號，

表 8：REGEXP_SUBSTR 函數 語法 說明 REGEXP_SUBSTR(source_string, pattern
[, position [, occurrence
[, match_parameter]]]) REGEXP_SUBSTR 函數返回匹配模式的子字符串。

表 9： REGEXP_REPLACE 函數 語法 說明 REGEXP_REPLACE(source_string, pattern
[, replace_string [, position
[,occurrence, [match_parameter]]]]) 該函數用一個指定的 replace_string 來替換匹配的模式，從而允許復雜的“搜索並替換”操作。

表 10：後向引用元字符 元字符 說明 \digit 反斜線 緊跟著一個 1 到 9 之間的數字，

表 11：模式交換正則表達式的說明 正則表達式項目 說明 ( 第一個子表達式的開頭 . 匹配除換行符之外的任意單字符 * 重復操作符，匹配之前的 . 元字符 0 到 n 次 ) 第一個子表達式的結尾；匹配結果在 \1
中獲取（在這個例子中，結果為 Ellen。） 必須存在的空白 ( 第二個子表達式的開頭 . 匹配除換行符之外的任意單個字符 * 重復操作符，匹配之前的 . 元字符 0 到 n 次 ) 第二個子表達式的結尾；匹配結果在 \2
中獲取（在這個例子中，結果為 Hildi。） 空白 ( 第三個子表達式的開頭 .

表 12：社會保險號碼正則表達式的說明 正則表達式項目 說明 ^ 行首字符（正則表達式在匹配之前不能有任何前導字符。） ( 開始子表達式並列出用 | 元字符分開的可替換選項 [ 字符列表的開頭 [:digit:] POSIX 數字類 ] 字符列表的結尾 {3} 字符列表正好重復出現 3 次 - 連字符 [ 字符列表的開頭 [:digit:] POSIX 數字類 ] 字符列表的結尾 {2} 字符列表正好重復出現 2 次

))

由 ^ 和 $ 指示的開頭或結尾的字符都是不可接受的。確保您的正則表達式沒有分成多行或包含任何不必要的空格，除非您希望格式如此並相應地進行匹配。表 12 說明了該正則表達式示例的各個組成部分。

訪問 Oracle Database 10g頁面：

將正則表達式與現有的功能進行比較

正則表達式有幾個優點優於常見的 LIKE 操作符和 INSTR、SUBSTR 及 REPLACE 函數的。這些傳統的 SQL 函數不便於進行模式匹配。只有 LIKE 操作符通過使用 % 和 _ 字符匹配，但 LIKE 不支持表達式的重復、復雜的更替、字符范圍、字符列表和 POSIX 字符類等等。此外，新的正則表達式函數允許檢測重復出現的單詞和模式交換。這裡的例子為您提供了正則表達式領域的一個概覽，以及您如何能夠在您的應用程序中使用它們。

實實在在地豐富您的工具包

因為正則表達式有助於解決復雜的問題，所以它們是非常強大的。正則表達式的一些功能難於用傳統的 SQL 函數來仿效。當您了解了這種稍顯神秘的語言的基礎構建程序塊時，正則表達式將成為您的工具包的不可缺少的一部分（不僅在 SQL 環境下也在其它的編程語言環境下）。為了使您的各個模式正確，雖然嘗試和錯誤有時是必須的，但正則表達式的簡潔和強大是不容置疑的。

Alice Rischert ([email protected]) 是哥倫比亞大學計算機技術與應用系的數據庫應用程序開發和設計方向的主席。她編寫了 Oracle SQL 交互手冊 第 2 版 （Prentice Hall，2002）和即將推出的 Oracle SQL 示例 （Prentice Hall，2003）。Rischert 擁有超過 15 年的經驗在財富 100 強公司內擔任數據庫設計師、DBA 和項目主管，並且她自從 Oracle version 5 起就一直使用 Oracle 產品。

表 1：定位元字符 元字符 說明 ^ 使表達式定位至一行的開頭 $ 使表達式定位至一行的末尾

表 2：量詞或重復操作符 量詞 說明 * 匹配 0 次或更多次 ? 匹配 0 次或 1 次 + 匹配 1 次或更多次 {m} 正好匹配 m 次 {m,} 至少匹配 m 次 {m, n} 至少匹配 m 次但不超過 n 次

表 3：預定義的 POSIX 字符類 字符類 說明 [:alpha:] 字母字符 [:lower:] 小寫字母字符 [:upper:] 大寫字母字符 [:digit:] 數字 [:alnum:] 字母數字字符

[:space:] 空白字符（禁止打印），如回車符、換行符、豎直制表符和換頁符 [:punct:] 標點字符 [:cntrl:] 控制字符（禁止打印） [:print:] 可打印字符

表 4：表達式的替換匹配和分組 元字符 說明 | 替換 分隔替換選項，通常與分組操作符 () 一起使用 ( ) 分組 將子表達式分組為一個替換單元、量詞單元或後向引用單元（參見“後向引用”部分） [char] 字符列表 表示一個字符列表；一個字符列表中的大多數元字符（除字符類、^ 和 - 元字符之外）被理解為文字

表 5：REGEXP_LIKE 操作符 語法 說明 REGEXP_LIKE(source_string, pattern
[, match_parameter])

表 6：REGEXP_INSTR 函數 語法 說明 REGEXP_INSTR(source_string, pattern
[, start_position
[, occurrence
[, return_option
[, match_parameter]]]]) 該函數查找 pattern ，並返回該模式的第一個位置。您可以隨意指定您想要開始搜索的 start_position。 occurrence 參數默認為 1，除非您指定您要查找接下來出現的一個模式。return_option 的默認值為 0，它返回該模式的起始位置；值為 1 則返回符合匹配條件的下一個字符的起始位置。

表 7： 5 位數字加 4 位郵政編碼表達式的說明 語法 說明   必須匹配的空白 [:digit:] POSIX 數字類 ] 字符列表的結尾 {5} 字符列表正好重復出現 5 次 ( 子表達式的開頭

表 8：REGEXP_SUBSTR 函數 語法 說明 REGEXP_SUBSTR(source_string, pattern
[, position [, occurrence
[, match_parameter]]]) REGEXP_SUBSTR 函數返回匹配模式的子字符串。

表 9： REGEXP_REPLACE 函數 語法

表 10：後向引用元字符 元字符 說明 \digit 反斜線 緊跟著一個 1 到 9 之間的數字，反斜線匹配之前的用括號括起來的第 digit 個子表達式。
（注意：反斜線在正則表達式中有另一種意義，取決於上下文，它還可能表示 Escape 字符。

表 11：模式交換正則表達式的說明 正則表達式項目 說明 ( 第一個子表達式的開頭 . 匹配除換行符之外的任意單字符 * 重復操作符，匹配之前的 . 元字符 0 到 n 次 ) 第一個子表達式的結尾；匹配結果在 \1
中獲取（在這個例子中，結果為 Ellen。） 必須存在的空白 (

表 12：社會保險號碼正則表達式的說明 正則表達式項目 說明 ^ 行首字符（正則表達式在匹配之前不能有任何前導字符。） ( 開始子表達式並列出用 | 元字符分開的可替換選項 [ 字符列表的開頭 [:digit:] PO