Oracle Database 10g 中的正規運算式特性是一個用於處理文本資料的強大工具
Oracle Database 10g 的一個新特性大大提高了您搜索和處理字元資料的能力。這個特性就是正規運算式，是一種用來描述文字模式的表示方法。很久以來它已在許多程式設計語言和大量 UNIX 實用工具中出現過了。
Oracle 的正規運算式的實施是以各種 SQL 函數和一個 WHERE 子句操作符的形式出現的。如果您不熟悉正規運算式，那麼這篇文章可以讓您瞭解一下這種新的極其強大然而表面上有點神秘的功能。已經對正規運算式很熟悉的讀者可以瞭解如何在 Oracle SQL 語言的環境中應用這種功能。
什麼是正規運算式？
正規運算式由一個或多個字元型文字和/或元字元組成。在最簡單的格式下，正規運算式僅由字元文字組成，如正規運算式 cat。它被讀作字母 c，接著是字母 a 和 t，這種模式匹配 cat、location 和 catalog 之類的字串。元字元提供演算法來確定 Oracle 如何處理組成一個正規運算式的字元。當您瞭解了各種元字元的含義時，您將體會到正規運算式用於查找和替換特定的文本資料是非常強大的。
驗證資料、識別重複關鍵字的出現、檢測不必要的空格，或分析字串只是正規運算式的許多應用中的一部分。您可以用它們來驗證電話號碼、郵遞區號、電子郵寄地址、社會安全號碼、IP 地址、檔案名和路徑名等的格式。此外，您可以查找如 HTML 標記、數位、日期之類的模式，或任意文本資料中符合任意模式的任何事物，並用其它的模式來替換它們。
用 Oracle Database 10g 使用正規運算式
您可以使用最新引進的 Oracle SQL REGEXP_LIKE 操作符和 REGEXP_INSTR、REGEXP_SUBSTR 以及 REGEXP_REPLACE 函數來發揮正規運算式的作用。您將體會到這個新的功能如何對 LIKE 操作符和 INSTR、SUBSTR 和 REPLACE 函數進行了補充。實際上，它們類似於已有的操作符，但現在增加了強大的模式匹配功能。被搜索的資料可以是簡單的字串或是存儲在資料庫字元列中的大量文本。正規運算式讓您能夠以一種您以前從未想過的方式來搜索、替換和驗證資料，並提供高度的靈活性。
正規運算式的基本例子
在使用這個新功能之前，您需要瞭解一些元字元的含義。句號 (.) 匹配一個正規運算式中的任意字元（除了分行符號）。例如，正規運算式 a.b 匹配的字串中首先包含字母 a，接著是其它任意單個字元（除了分行符號），再接著是字母 b。字串 axb、xaybx 和 abba 都與之匹配，因為在字串中隱藏了這種模式。如果您想要精確地匹配以 a 開頭和以 b 結尾的一條三個字母的字串，則您必須對正規運算式進行定位。脫字元號 (^) 元字元指示一行的開始，而美元符號 ($) 指示一行的結尾（參見表1：附表見第4頁）。因此， 正規運算式 ^a.b$ 匹配字串 aab、abb 或 axb。將這種方式與 LIKE 操作符提供的類似的模式匹配 a_b 相比較，其中 (_) 是單字元萬用字元。
預設情況下，一個正規運算式中的一個單獨的字元或字元清單只匹配一次。為了指示在一個正規運算式中多次出現的一個字元，您可以使用一個量詞，它也被稱為重複操作符。.如果您想要得到從字母 a 開始並以字母 b 結束的匹配模式，則您的正規運算式看起來像這樣：^a.*b$。* 元字元重複前面的元字元 (.) 指示的匹配零次、一次或更多次。LIKE 操作符的等價的模式是 a%b，其中用百分號 (%) 來指示任意字元出現零次、一次或多次。
表 2 給出了重複操作符的完整列表。注意它包含了特殊的重複選項，它們實現了比現有的 LIKE 萬用字元更大的靈活性。如果您用圓括號括住一個運算式，這將有效地創建一個可以重複一定次數的子運算式。例如，正規運算式 b(an)*a 匹配 ba、bana、banana、yourbananasplit 等。
Oracle 的正規運算式實施支援 POSIX （可移植作業系統介面）字元類，參見表 3 中列出的內容。這意味著您要查找的字元類型可以非常特別。假設您要編寫一條僅查找非字母字元的 LIKE 條件 — 作為結果的 WHERE 子句可能不經意就會變得非常複雜。
POSIX 字元類必須包含在一個由方括號 ([]) 指示的字元清單中。例如，正規運算式 [[:lower:]] 匹配一個小寫字母字元，而 [[:lower:]]{5} 匹配五個連續的小寫字母字元。
除 POSIX 字元類之外，您可以將單獨的字元放在一個字元清單中。例如，正規運算式 ^ab[cd]ef$ 匹配字串 abcef 和 abdef。必須選擇 c 或 d。
除脫字元 (^) 和連字號 (-) 之外，字元清單中的大多數元字元被認為是文字。正規運算式看起來很複雜，這是因為一些元字元具有隨上下文環境而定的多重含義。^ 就是這樣一種元字元。如果您用它作為一個字元清單的第一個字元，它代表一個字元清單的非。因此，[^[:digit:]] 查找包含了任意非數位字元的模式，而 ^[[:digit:]] 查找以數位開始的匹配模式。連字號 (-) 指示一個範圍，正規運算式 [a-m] 匹配字母 a 到字母 m 之間的任意字母。但如果它是一個字元行中的第一個字元（如在 [-afg] 中），則它就代表連字號。
之前的一個例子介紹了使用圓括號來創建一個子運算式；它們允許您通過輸入更替元字元來輸入可更替的選項，這些元字元由分隔號 (|) 分開。
例如，正規運算式 t(a|e|i)n 允許字母 t 和 n 之間的三種可能的字元更替。匹配模式包括如 tan、ten、tin 和 Pakistan 之類的字，但不包括 teen、mountain 或 tune。作為另一種選擇，正規運算式 t(a|e|i)n 也可以表示為一個字元清單 t[aei]n。表 4 匯總了這些元字元。雖然存在更多的元字元，但這個簡明的概述足夠用來理解這篇文章使用的正規運算式。
Oracle Database 10g 的一個新特性大大提高了您搜索和處理字元資料的能力。這個特性就是正規運算式，是一種用來描述文字模式的表示方法。很久以來它已在許多程式設計語言和大量 UNIX 實用工具中出現過了。
Oracle 的正規運算式的實施是以各種 SQL 函數和一個 WHERE 子句操作符的形式出現的。如果您不熟悉正規運算式，那麼這篇文章可以讓您瞭解一下這種新的極其強大然而表面上有點神秘的功能。已經對正規運算式很熟悉的讀者可以瞭解如何在 Oracle SQL 語言的環境中應用這種功能。
什麼是正規運算式？
正規運算式由一個或多個字元型文字和/或元字元組成。在最簡單的格式下，正規運算式僅由字元文字組成，如正規運算式 cat。它被讀作字母 c，接著是字母 a 和 t，這種模式匹配 cat、location 和 catalog 之類的字串。元字元提供演算法來確定 Oracle 如何處理組成一個正規運算式的字元。當您瞭解了各種元字元的含義時，您將體會到正規運算式用於查找和替換特定的文本資料是非常強大的。
驗證資料、識別重複關鍵字的出現、檢測不必要的空格，或分析字串只是正規運算式的許多應用中的一部分。您可以用它們來驗證電話號碼、郵遞區號、電子郵寄地址、社會安全號碼、IP 地址、檔案名和路徑名等的格式。此外，您可以查找如 HTML 標記、數位、日期之類的模式，或任意文本資料中符合任意模式的任何事物，並用其它的模式來替換它們。
用 Oracle Database 10g 使用正規運算式
您可以使用最新引進的 Oracle SQL REGEXP_LIKE 操作符和 REGEXP_INSTR、REGEXP_SUBSTR 以及 REGEXP_REPLACE 函數來發揮正規運算式的作用。您將體會到這個新的功能如何對 LIKE 操作符和 INSTR、SUBSTR 和 REPLACE 函數進行了補充。實際上，它們類似於已有的操作符，但現在增加了強大的模式匹配功能。被搜索的資料可以是簡單的字串或是存儲在資料庫字元列中的大量文本。正規運算式讓您能夠以一種您以前從未想過的方式來搜索、替換和驗證資料，並提供高度的靈活性。
正規運算式的基本例子
在使用這個新功能之前，您需要瞭解一些元字元的含義。句號 (.) 匹配一個正規運算式中的任意字元（除了分行符號）。例如，正規運算式 a.b 匹配的字串中首先包含字母 a，接著是其它任意單個字元（除了分行符號），再接著是字母 b。字串 axb、xaybx 和 abba 都與之匹配，因為在字串中隱藏了這種模式。如果您想要精確地匹配以 a 開頭和以 b 結尾的一條三個字母的字串，則您必須對正規運算式進行定位。脫字元號 (^) 元字元指示一行的開始，而美元符號 ($) 指示一行的結尾（參見表1：附表見第4頁）。因此， 正規運算式 ^a.b$ 匹配字串 aab、abb 或 axb。將這種方式與 LIKE 操作符提供的類似的模式匹配 a_b 相比較，其中 (_) 是單字元萬用字元。
預設情況下，一個正規運算式中的一個單獨的字元或字元清單只匹配一次。為了指示在一個正規運算式中多次出現的一個字元，您可以使用一個量詞，它也被稱為重複操作符。.如果您想要得到從字母 a 開始並以字母 b 結束的匹配模式，則您的正規運算式看起來像這樣：^a.*b$。* 元字元重複前面的元字元 (.) 指示的匹配零次、一次或更多次。LIKE 操作符的等價的模式是 a%b，其中用百分號 (%) 來指示任意字元出現零次、一次或多次。
表 2 給出了重複操作符的完整列表。注意它包含了特殊的重複選項，它們實現了比現有的 LIKE 萬用字元更大的靈活性。如果您用圓括號括住一個運算式，這將有效地創建一個可以重複一定次數的子運算式。例如，正規運算式 b(an)*a 匹配 ba、bana、banana、yourbananasplit 等。
Oracle 的正規運算式實施支援 POSIX （可移植作業系統介面）字元類，參見表 3 中列出的內容。這意味著您要查找的字元類型可以非常特別。假設您要編寫一條僅查找非字母字元的 LIKE 條件 — 作為結果的 WHERE 子句可能不經意就會變得非常複雜。
POSIX 字元類必須包含在一個由方括號 ([]) 指示的字元清單中。例如，正規運算式 [[:lower:]] 匹配一個小寫字母字元，而 [[:lower:]]{5} 匹配五個連續的小寫字母字元。
除 POSIX 字元類之外，您可以將單獨的字元放在一個字元清單中。例如，正規運算式 ^ab[cd]ef$ 匹配字串 abcef 和 abdef。必須選擇 c 或 d。
除脫字元 (^) 和連字號 (-) 之外，字元清單中的大多數元字元被認為是文字。正規運算式看起來很複雜，這是因為一些元字元具有隨上下文環境而定的多重含義。^ 就是這樣一種元字元。如果您用它作為一個字元清單的第一個字元，它代表一個字元清單的非。因此，[^[:digit:]] 查找包含了任意非數位字元的模式，而 ^[[:digit:]] 查找以數位開始的匹配模式。連字號 (-) 指示一個範圍，正規運算式 [a-m] 匹配字母 a 到字母 m 之間的任意字母。但如果它是一個字元行中的第一個字元（如在 [-afg] 中），則它就代表連字號。
之前的一個例子介紹了使用圓括號來創建一個子運算式；它們允許您通過輸入更替元字元來輸入可更替的選項，這些元字元由分隔號 (|) 分開。
例如，正規運算式 t(a|e|i)n 允許字母 t 和 n 之間的三種可能的字元更替。匹配模式包括如 tan、ten、tin 和 Pakistan 之類的字，但不包括 teen、mountain 或 tune。作為另一種選擇，正規運算式 t(a|e|i)n 也可以表示為一個字元清單 t[aei]n。表 4 匯總了這些元字元。雖然存在更多的元字元，但這個簡明的概述足夠用來理解這篇文章使用的正規運算式。
REGEXP_LIKE 操作符
REGEXP_LIKE 操作符向您介紹在 Oracle 資料庫中使用時的正規運算式功能。表 5 列出了 REGEXP_LIKE 的語法。
下面的 SQL 查詢的 WHERE 子句顯示了 REGEXP_LIKE 操作符，它在 ZIP 列中搜索滿足正規運算式 [^[:digit:]] 的模式。它將檢索 ZIPCODE 表中的那些 ZIP 列值包含了任意非數位字元的行。
SELECT zip FROM zipcode WHERE REGEXP_LIKE(zip, '[^[:digit:]]')
ZIP
-----
ab123
123xy
007ab
abcxy
這個正規運算式的例子僅由元字元組成，更具體來講是被冒號和方括號分隔的 POSIX 字元類 digit。第二組方括號（如 [^[:digit:]] 中所示）包括了一個字元類清單。如前文所述，需要這樣做是因為您只可以將 POSIX 字元類用於構建一個字元清單。
REGEXP_INSTR 函數
這個函數返回一個模式的起始位置，因此它的功能非常類似於 INSTR 函數。新的 REGEXP_INSTR 函數的語法在表 6 中給出。這兩個函數之間的主要區別是，REGEXP_INSTR 讓您指定一種模式，而不是一個特定的搜索字串；因而它提供了更多的功能。接下來的示例使用 REGEXP_INSTR 來返回字串 Joe Smith, 10045 Berry Lane, San Joseph, CA 91234 中的五位元郵遞區號模式的起始位置。如果正規運算式被寫為 [[:digit:]]{5}，則您將得到門牌號的起始位置而不是郵遞區號的，因為 10045 是第一次出現五個連續數字。因此，您必須將運算式定位到該行的末尾，正如 $ 元字元所示，該函數將顯示郵遞區號的起始位置，而不管門牌號的數字個數。
SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234',
'[[:digit:]]{5}$') AS rx_instr FROM dual
RX_INSTR
----------
45
編寫更複雜的模式
讓我們在前一個例子的郵遞區號模式上展開，以便包含一個可選的四位元數位模式。您的模式現在可能看起來像這樣：[[:digit:]]{5}(-[[:digit:]]{4})?$。如果您的源字串以 5 位元郵遞區號或 5 位 + 4 位郵遞區號的格式結束，則您將能夠顯示該模式的起始位置。
SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234-1234',
' [[:digit:]]{5}(-[[:digit:]]{4})?$') AS starts_at FROM dual
STARTS_AT
----------
44
在這個示例中，括弧裡的子運算式 (-[[: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
REGEXP_LIKE 操作符向您介紹在 Oracle 資料庫中使用時的正規運算式功能。表 5 列出了 REGEXP_LIKE 的語法。
下面的 SQL 查詢的 WHERE 子句顯示了 REGEXP_LIKE 操作符，它在 ZIP 列中搜索滿足正規運算式 [^[:digit:]] 的模式。它將檢索 ZIPCODE 表中的那些 ZIP 列值包含了任意非數位字元的行。
SELECT zip FROM zipcode WHERE REGEXP_LIKE(zip, '[^[:digit:]]')
ZIP
-----
ab123
123xy
007ab
abcxy
這個正規運算式的例子僅由元字元組成，更具體來講是被冒號和方括號分隔的 POSIX 字元類 digit。第二組方括號（如 [^[:digit:]] 中所示）包括了一個字元類清單。如前文所述，需要這樣做是因為您只可以將 POSIX 字元類用於構建一個字元清單。
REGEXP_INSTR 函數
這個函數返回一個模式的起始位置，因此它的功能非常類似於 INSTR 函數。新的 REGEXP_INSTR 函數的語法在表 6 中給出。這兩個函數之間的主要區別是，REGEXP_INSTR 讓您指定一種模式，而不是一個特定的搜索字串；因而它提供了更多的功能。接下來的示例使用 REGEXP_INSTR 來返回字串 Joe Smith, 10045 Berry Lane, San Joseph, CA 91234 中的五位元郵遞區號模式的起始位置。如果正規運算式被寫為 [[:digit:]]{5}，則您將得到門牌號的起始位置而不是郵遞區號的，因為 10045 是第一次出現五個連續數字。因此，您必須將運算式定位到該行的末尾，正如 $ 元字元所示，該函數將顯示郵遞區號的起始位置，而不管門牌號的數字個數。
SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234',
'[[:digit:]]{5}$') AS rx_instr FROM dual
RX_INSTR
----------
45
編寫更複雜的模式
讓我們在前一個例子的郵遞區號模式上展開，以便包含一個可選的四位元數位模式。您的模式現在可能看起來像這樣：[[:digit:]]{5}(-[[:digit:]]{4})?$。如果您的源字串以 5 位元郵遞區號或 5 位 + 4 位郵遞區號的格式結束，則您將能夠顯示該模式的起始位置。
SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234-1234',
' [[:digit:]]{5}(-[[:digit:]]{4})?$') AS starts_at FROM dual
STARTS_AT
----------
44
在這個示例中，括弧裡的子運算式 (-[[: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
後向引用 規則運算式的一個有用的特性是能夠存儲子運算式供以後重用；這也被稱為後向引用（在表 10 中對其進行了概述）。它允許複雜的替換功能，如在新的位置上交換模式或顯示重複出現的單詞或字母。子運算式的匹配部分保存在臨時緩衝區中。緩衝區從左至右進行編號，並利用 \digit 符號進行訪問，其中 digit 是 1 到 9 之間的一個數字，它匹配第 digit 個子運算式，子運算式用一組圓括號來顯示。
接下來的例子顯示了通過按編號引用各個子運算式將姓名 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 說明了該規則運算式示例的各個組成部分。
將規則運算式與現有的功能進行比較
規則運算式有幾個優點優於常見的 LIKE 操作符和 INSTR、SUBSTR 及 REPLACE 函數的。這些傳統的 SQL 函數不便於進行模式匹配。只有 LIKE 操作符通過使用 % 和 _ 字元匹配，但 LIKE 不支援運算式的重複、複雜的更替、字元範圍、字元清單和 POSIX 字元類等等。此外，新的規則運算式函數允許檢測重複出現的單詞和模式交換。這裡的例子為您提供了規則運算式領域的一個概覽，以及您如何能夠在您的應用程式中使用它們。
實實在在地豐富您的工具包
因為規則運算式有助於解決複雜的問題，所以它們是非常強大的。規則運算式的一些功能難於用傳統的 SQL 函數來仿效。當您瞭解了這種稍顯神秘的語言的基礎構建區塊時，規則運算式將成為您的工具包的不可缺少的一部分（不僅在 SQL 環境下也在其它的程式設計語言環境下）。為了使您的各個模式正確，雖然嘗試和錯誤有時是必須的，但規則運算式的簡潔和強大是不容置疑的。
Alice Rischert (ar280@yahoo.com) 是哥倫比亞大學電腦技術與應用系的資料庫應用程式開發和設計方向的主席。她編寫了 Oracle SQL 交互手冊 第 2 版（Prentice Hall，2002）和即將推出的 Oracle SQL 示例 （Prentice Hall，2003）。Rischert 擁有超過 15 年的經驗在財富 100 強公司內擔任資料庫設計師、DBA 和專案主管，並且她自從 Oracle version 5 起就一直使用 Oracle 產品。
接下來的例子顯示了通過按編號引用各個子運算式將姓名 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 說明了該規則運算式示例的各個組成部分。
將規則運算式與現有的功能進行比較
規則運算式有幾個優點優於常見的 LIKE 操作符和 INSTR、SUBSTR 及 REPLACE 函數的。這些傳統的 SQL 函數不便於進行模式匹配。只有 LIKE 操作符通過使用 % 和 _ 字元匹配，但 LIKE 不支援運算式的重複、複雜的更替、字元範圍、字元清單和 POSIX 字元類等等。此外，新的規則運算式函數允許檢測重複出現的單詞和模式交換。這裡的例子為您提供了規則運算式領域的一個概覽，以及您如何能夠在您的應用程式中使用它們。
實實在在地豐富您的工具包
因為規則運算式有助於解決複雜的問題，所以它們是非常強大的。規則運算式的一些功能難於用傳統的 SQL 函數來仿效。當您瞭解了這種稍顯神秘的語言的基礎構建區塊時，規則運算式將成為您的工具包的不可缺少的一部分（不僅在 SQL 環境下也在其它的程式設計語言環境下）。為了使您的各個模式正確，雖然嘗試和錯誤有時是必須的，但規則運算式的簡潔和強大是不容置疑的。
Alice Rischert (ar280@yahoo.com) 是哥倫比亞大學電腦技術與應用系的資料庫應用程式開發和設計方向的主席。她編寫了 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 | source_string 支援字元資料類型（CHAR、VARCHAR2、CLOB、NCHAR、NVARCHAR2 和NCLOB，但不包括 LONG）。pattern 參數是規則運算式的另一個名稱。match_parameter允許可選的參數（如處理分行符號、保留多行格式化以及提供對區分大小寫的控制）。 | |
| [, match_parameter]) | ||
| 表 6：REGEXP_INSTR 函數 | ||
| 語法 | 說明 | |
| REGEXP_INSTR(source_string, pattern | 該函數查找 pattern ，並返回該模式的第一個位置。您可以隨意指定您想要開始搜索的start_position。 occurrence 參數默認為 1，除非您指定您要查找接下來出現的一個模式。return_option 的預設值為 0，它返回該模式的起始位置；值為 1 則返回符合匹配條件的下一個字元的起始位置。 | |
| [, start_position | ||
| [, occurrence | ||
| [, return_option | ||
| [, match_parameter]]]]) | ||
| 表 7： 5 位元數字加 4 位元郵遞區號運算式的說明 | ||
| 語法 | 說明 | |
| | 必須匹配的空白 | |
| [:digit:] | POSIX 數字類 | |
| ] | 字元清單的結尾 | |
| {5} | 字元清單正好重複出現 5 次 | |
| ( | 子運算式的開頭 | |
| - | 一個文字連字號，因為它不是一個字元清單內的範圍元字元 | |
| [ | 字元清單的開頭 | |
| [:digit:] | POSIX [:digit:]類 | |
| [ | 字元清單的開頭 | |
| ] | 字元清單的結尾 | |
| {4} | 字元清單正好重複出現 4 次 | |
| ) | 結束圓括號，結束子運算式 | |
| ? | ? 量詞匹配分組的子運算式 0 或 1 次，從而使得 4 位代碼可選 | |
| $ | 定位元字元，指示行尾 | |
| 表 8：REGEXP_SUBSTR 函數 | ||
| 語法 | 說明 | |
| REGEXP_SUBSTR(source_string, pattern | REGEXP_SUBSTR 函數返回匹配模式的子字串。 | |
| [, position [, occurrence | ||
| [, match_parameter]]]) | ||
| 表 9： REGEXP_REPLACE 函數 | ||
| 語法 | 說明 | |
| REGEXP_REPLACE(source_string, pattern | 該函數用一個指定的 replace_string 來替換匹配的模式，從而允許複雜的“搜索並替換”操作。 | |
| [, replace_string [, position | ||
| [,occurrence, [match_parameter]]]]) | ||
| 表 10：後向引用元字元 | ||
| 元字元 | 說明 | |
| \digit | 反斜線 | 緊跟著一個 1 到 9 之間的數字，反斜線匹配之前的用括弧括起來的第 digit 個子運算式。 |
| （注意：反斜線在規則運算式中有另一種意義，取決於上下文，它還可能表示 Escape 字元。 | ||
| 表 11：模式交換規則運算式的說明 | ||
| 規則運算式項目 | 說明 | |
| ( | 第一個子運算式的開頭 | |
| . | 匹配除分行符號之外的任意單字元 | |
| * | 重複操作符，匹配之前的 . 元字元 0 到 n 次 | |
| ) | 第一個子運算式的結尾；匹配結果在 \1 | |
| 中獲取（在這個例子中，結果為 Ellen。） | ||
| | 必須存在的空白 | |
| ( | 第二個子運算式的開頭 | |
| . | 匹配除分行符號之外的任意單個字元 | |
| * | 重複操作符，匹配之前的 . 元字元 0 到 n 次 | |
| ) | 第二個子運算式的結尾；匹配結果在 \2 | |
| 中獲取（在這個例子中，結果為 Hildi。） | ||
| | 空白 | |
| ( | 第三個子運算式的開頭 | |
| . | 匹配除分行符號之外的任意單字元 | |
| * | 重複操作符，匹配之前的 . 元字元 0 到 n 次 | |
| ) | 第三個子運算式的結尾；匹配結果在 \3 | |
| 中獲取（在這個例子中，結果為 Smith。） | ||
| 表 12：社會保險號碼規則運算式的說明 | ||
| 規則運算式項目 | 說明 | |
| ^ | 行首字元（規則運算式在匹配之前不能有任何前導字元。） | |
| ( | 開始子運算式並列出用 | 元字元分開的可替換選項 | |
| [ | 字元清單的開頭 | |
| [:digit:] | POSIX 數字類 | |
| ] | 字元清單的結尾 | |
| {3} | 字元清單正好重複出現 3 次 | |
| - | 連字號 | |
| [ | 字元清單的開頭 | |
| [:digit:] | POSIX 數字類 | |
| ] | 字元清單的結尾 | |
| {2} | 字元清單正好重複出現 2 次 | |
| - | 另一個連字號 | |
| [ | 字元清單的開頭 | |
| [:digit:] | POSIX 數字類 | |
| ] | 字元清單的結尾 | |
| {4} | 字元清單正好重複出現 4 次 | |
| | | 替換元字元；結束第一個選項並開始下一個替換運算式 | |
| [ | 字元清單的開頭 | |
沒有留言:
張貼留言