Oracle Database 正規表示式

從 10g 第 1 版開始，Oracle Database 提供 4 個 regexp 函數，您可以在 SQL 和 PL/SQL 陳述式中使用。這些函數實作 POSIX 延伸正規表示式 (ERE) 標準。Oracle 完全支援校對順序和等價類別，用於方括號表示式。NLS_SORT 設定會決定所使用的 POSIX 區域設定，而區域設定會決定可用的校對順序和等價類別。

不過，Oracle 並未完全實作 POSIX ERE 標準。它在三個方面有所不同。首先，Oracle 支援正規表示式中 \1 到 \9 的反向參照。POSIX ERE 標準不支援這些，儘管 POSIX BRE 支援。在完全相容的引擎中，\1 到 \9 會是非法的。POSIX 標準指出，使用反斜線來跳脫非元字元的字元是非法的。Oracle 允許這樣做，並只會忽略反斜線。例如，\q 在 Oracle 中與 q 相同。結果是所有 POSIX ERE 正規表示式都可以在 Oracle 中使用，但一些在 Oracle 中有效的正規表示式可能會在完全相容 POSIX 的引擎中造成錯誤。顯然地，如果您只使用 Oracle，這些差異就無關緊要。

第三個差異比較細微。它不會造成任何錯誤，但可能會導致不同的比對。正如我在 POSIX 標準主題中所解釋的，它要求 regex 引擎在交替的情況下傳回最長的比對。Oracle 的引擎不會這樣做。它是一個傳統 NFA 引擎，就像本網站上討論的所有非 POSIX regex 風格一樣。

如果您曾在其他程式語言中使用過正規表示式，請注意 POSIX 不支援非可列印字元的跳脫字元，例如 tab 的 \t 或換行符號的 \n。您可以在 C++ 等程式語言的 POSIX 引擎中使用這些字元，因為 C++ 編譯器會將字串常數中的 \t 和 \n 解釋為 tab 和換行符號。在 SQL 陳述式中，您需要在字串中輸入實際的 tab 或換行符號，並搭配您的正規表示式才能與 tab 或換行符號相符。Oracle 的正規表示式引擎會將字串 '\t' 解釋為正規表示式 t，當傳遞為 regexp 參數時。

Oracle 10g R2 進一步擴充正規表示式語法，新增了自由間隔模式（不支援註解）、簡寫字元類別、惰性量詞，以及錨點 \A、\Z 和 \z。Oracle 11g 和 12c 使用與 10g R2 相同的正規表示式風格。

Oracle 的 REGEXP 函式

Oracle Database 10g 提供四個正規表示式函式。您可以在 SQL 和 PL/SQL 陳述式中平等地使用這些函式。

REGEXP_LIKE(source, regexp, modes) 可能會是您最常使用的一個。您可以在 SELECT 陳述式的 WHERE 和 HAVING 子句中使用它。在 PL/SQL 腳本中，它會傳回一個布林值。您也可以在 CHECK 約束中使用它。source 參數是正規表示式應該與之相符的字串或欄位。regexp 參數是包含正規表示式的字串。modes 參數是選用的。它設定相符模式。

SELECT * FROM mytable WHERE REGEXP_LIKE(mycolumn, 'regexp', 'i');
IF REGEXP_LIKE('subject', 'regexp') THEN /* Match */ ELSE /* No match */ END IF;
ALTER TABLE mytable ADD (CONSTRAINT mycolumn_regexp CHECK (REGEXP_LIKE(mycolumn, '^regexp$')));

REGEXP_SUBSTR(source, regexp, position, occurrence, modes) 傳回一個字串，其中包含由正規表示式相符的 source 部分。如果相符嘗試失敗，則傳回 NULL。您可以將 REGEXP_SUBSTR 與單一字串或欄位一起使用。您可以在 SELECT 子句中使用它，以僅擷取欄位的特定部分。position 參數指定相符嘗試應該開始的來源字串中的字元位置。第一個字元的位址為 1。occurrence 參數指定要取得哪個相符。設定為 1 以取得第一個相符。如果您指定較高的數字，Oracle 會繼續嘗試從前一個相符的結尾開始相符正規表示式，直到找到與您指定的相符數目一樣多的相符。然後傳回最後一個相符。如果相符數目較少，則傳回 NULL。請勿將此參數與反向參照混淆。Oracle 未提供函式來傳回由擷取群組相符的字串部分。最後三個參數是選用的。

SELECT REGEXP_SUBSTR(mycolumn, 'regexp') FROM mytable;
match := REGEXP_SUBSTR('subject', 'regexp', 1, 1, 'i')

REGEXP_REPLACE(source, regexp, replacement, position, occurrence, modes) 傳回來源字串，其中一個或所有正規表示式相符都已取代。如果找不到相符，則取代原始字串。如果您為 occurrence 指定正數（請參閱上段），則只會取代該相符。如果您指定零或略過參數，則會取代所有相符。最後三個參數是選用的。replacement 參數是每個正規表示式相符都會被取代的字串。您可以在取代文字中使用反向參照 \1 到 \9，以重新插入由擷取群組相符的文字。您可以多次參照同一個群組。沒有取代文字代碼可以重新插入整個正規表示式相符。若要執行此操作，請在整個正規表示式中加上括號，並在取代中使用 \1。如果您想要插入文字 \1，請使用字串 '\\1'。只有在反斜線後接數字或另一個反斜線時，才需要跳脫反斜線。若要插入文字 \\，請使用字串 '\\\\'。雖然 SQL 不要求在字串中跳脫反斜線，但 REGEXP_REPLACE 函式需要。

SELECT REGEXP_REPLACE(mycolumn, 'regexp', 'replacement') FROM mytable;
result := REGEXP_REPLACE('subject', 'regexp', 'replacement', 1, 0, 'i');

REGEXP_INSTR(來源, 正規表示式, 位置, 出現次數, 回傳選項, 模式) 回傳來源字串中正規表示式比對的開頭或結尾位置。此函式採用與 REGEXP_SUBSTR 相同的參數，再加一個。設定 return_option 為零或省略參數，以取得比對中第一個字元的位址。設定為一，以取得比對後第一個字元的位址。字串中的第一個字元為位址 1。如果找不到比對，REGEXP_INSTR 會回傳零。最後 4 個參數為選用。

SELECT REGEXP_INSTR(mycolumn, 'regexp', 1, 1, 0, 'i') FROM mytable;

REGEXP_COUNT(來源, 正規表示式, 位置, 模式) 回傳正規表示式可以在來源字串中比對到的次數。如果正規表示式完全找不到比對，則會回傳零。此函式僅在 Oracle 11g 及更新版本中提供。

SELECT REGEXP_COUNT(mycolumn, 'regexp', 1, 'i') FROM mytable;

Oracle 的比對模式

四個正規表示式函式所接受的 modes 參數應為包含最多三個字元的字串，共四個可能的字元。例如，'i' 會開啟不分大小寫的比對，而 'inm' 會開啟這三個選項。'i' 和 'c' 互斥。如果您省略此參數或傳遞空字串，則會使用預設的比對模式。

'i': 開啟不分大小寫的比對。預設值取決於 NLS_SORT 設定。
'c': 開啟分大小寫的比對。預設值取決於 NLS_SORT 設定。
'n': 使點與任何字元比對，包括換行符號。預設情況下，點與任何字元比對，但排除換行符號。
'm': 使插入符號和美元符號與每一行的開頭和結尾比對（即在來源字串中嵌入的換行符號之後和之前）。預設情況下，這些符號只與字串的最開頭和最結尾比對。
'x': 開啟空白模式，忽略字元類別之外的任何未跳脫空白（10gR2 及更新版本）。

進一步閱讀

O’Reilly 的書籍 Oracle Regular Expressions Pocket Reference 是一本非常實用的 64 頁書籍，告訴您在 Oracle 資料庫 10g 中關於正規表示式需要知道的一切。儘管書的封面，它實際上包含教學和參考。由於 Oracle 的正規表示式支援相當有限，因此這本小書是您成功使用 Oracle 正規表示式所需的一切。

我的 Oracle Regular Expressions Pocket Reference 書籍評論

| 快速開始 | 教學 | 工具與語言 | 範例 | 參考 | 書籍評論 |

| EditPad Lite | EditPad Pro |

| MySQL | Oracle | PostgreSQL |