REGEXP_Replace使用方法

1、REGEXP_REPLACE 函數(shù) 讓我們首先看一下傳統(tǒng)的 REPLACE SQL 函數(shù)，它把一個字符串用另一個字符串來替換。假設(shè)您的數(shù)據(jù)在正文中有不必要的空格，您希望用單個空格來替換它們。利用 REPLACE 函數(shù)，您需要準(zhǔn)確地列出您要替換多少個空格。然而，多余空格的數(shù)目在正文的各處可能不是相同的。下面的示例在 Joe 和 Smith 之間有三個空格。REPLACE 函數(shù)的參數(shù)指定要用一個空格來替換兩個空格。在這種情況下，結(jié)果在原來的字符串的 Joe 和

2、 Smith 之間留下了一個額外的空格。 SELECT REPLACE('Joe Smith',' ', ' ')AS replaceFROM dualREPLACE-Joe Smith REGEXP_REPLACE 函數(shù)把替換功能向前推進(jìn)了一步，其語法在表 9 中列出。以下查詢用單個空格替換了任意兩個或更多的空格。( ) 子表達(dá)式包含了單個空格，它可以按 2,

3、0;的指示重復(fù)兩次或更多次。 表 9： REGEXP_REPLACE 函數(shù)  語法說明REGEXP_REPLACE(source_string, pattern, replace_string , position,occurrence, match_parameter)該函數(shù)用一個指定的 replace_string 來替換匹配的模式，從而允許復(fù)雜的"搜索并替換"操作。SELECT REGEXP_REPLACE('Joe

4、60;Smith','( )2,', ' ')AS RX_REPLACEFROM dualRX_REPLACE-Joe Smith 后向引用 正則表達(dá)式的一個有用的特性是能夠存儲子表達(dá)式供以后重用；這也被稱為后向引用（在表 10 中對其進(jìn)行了概述）。它允許復(fù)雜的替換功能，如在新的位置上交換模式或顯示重復(fù)出現(xiàn)的單詞或字母。子表達(dá)式的匹配部分保存在臨時緩沖區(qū)中。緩沖區(qū)從左至右進(jìn)行編號，并利用 digit 符號進(jìn)行訪問，其中 dig

5、it 是 1 到 9 之間的一個數(shù)字，它匹配第 digit 個子表達(dá)式，子表達(dá)式用一組圓括號來顯示。 接下來的例子顯示了通過按編號引用各個子表達(dá)式將姓名 Ellen Hildi Smith 轉(zhuǎn)變?yōu)?#160;Smith, Ellen Hildi。 SELECT REGEXP_REPLACE('Ellen Hildi Smith','(.*) (.*) (.*)',&

6、#160;'3, 1 2')FROM dualREGEXP_REPLACE('EL-Smith, Ellen Hildi 該 SQL 語句顯示了用圓括號括住的三個單獨的子表達(dá)式。每一個單獨的子表達(dá)式包含一個匹配元字符 (.)，并緊跟著 * 元字符，表示任何字符（除換行符之外）都必須匹配零次或更多次?？崭駥⒏鱾€子表達(dá)式分開，空格也必須匹配。圓括號創(chuàng)建獲取值的子表達(dá)式，并且可以用 digit 來引用。第一個子表達(dá)式被賦值為 1 

7、;，第二個 2，以此類推。這些后向引用被用在這個函數(shù)的最后一個參數(shù) (3, 1 2) 中，這個函數(shù)有效地返回了替換子字符串，并按期望的格式來排列它們（包括逗號和空格）。表 11 詳細(xì)說明了該正則表達(dá)式的各個組成部分。 后向引用對替換、格式化和代替值非常有用，并且您可以用它們來查找相鄰出現(xiàn)的值。接下來的例子顯示了使用 REGEP_SUBSTR 函數(shù)來查找任意被空格隔開的重復(fù)出現(xiàn)的字母數(shù)字值。顯示的結(jié)果給出了識別重復(fù)出現(xiàn)的單詞 is 的子字符串。 SELECT 

8、;REGEXP_SUBSTR('The final test is is the implementation',(:alnum:+)(:space:+)1') AS substrFROM dualSUBSTR-is is 匹配參數(shù)選項 您可能已經(jīng)注意到了正則表達(dá)式操作符和函數(shù)包含一個可選的匹配參數(shù)。這個參數(shù)控制是否區(qū)分大小寫、換行符的匹配和保留多行輸入。 正則表達(dá)式的實際應(yīng)用 您不僅可以在隊列中使用正則表達(dá)式，還可以在使用

9、0;SQL 操作符或函數(shù)的任何地方（比如說在 PL/SQL 語言中）使用正則表達(dá)式。您可以編寫利用正則表達(dá)式功能的觸發(fā)器，以驗證、生成或提取值。 接下來的例子演示了您如何能夠在一次列檢查約束條件中應(yīng)用 REGEXP_LIKE 操作符來進(jìn)行數(shù)據(jù)驗證。它在插入或更新時檢驗正確的社會保險號碼格式。如 123-45-6789 和 123456789 之類格式的社會保險號碼對于這種列約束條件是可接受的值。有效的數(shù)據(jù)必須以三個數(shù)字開始，緊跟著一個連字符，再加兩個數(shù)字和一個連字符，最后又是四個數(shù)字。另一種表達(dá)

10、式只允許 9 個連續(xù)的數(shù)字。豎線符號 (|) 將各個選項分開。 ALTER TABLE students  ADD CONSTRAINT stud_ssn_ck CHECK  (REGEXP_LIKE(ssn,  '(:digit:3-:digit:2-:digit:4|:digit:9)$')由  和 $ 指示的開頭或結(jié)尾的字符都是不可接受的。確保您的正則表達(dá)式?jīng)]有分成多行或包含

11、任何不必要的空格，除非您希望格式如此并相應(yīng)地進(jìn)行匹配。表 12 說明了該正則表達(dá)式示例的各個組成部分。 將正則表達(dá)式與現(xiàn)有的功能進(jìn)行比較 正則表達(dá)式有幾個優(yōu)點優(yōu)于常見的 LIKE 操作符和 INSTR、SUBSTR 及 REPLACE 函數(shù)的。這些傳統(tǒng)的 SQL 函數(shù)不便于進(jìn)行模式匹配。只有 LIKE 操作符通過使用 % 和 _ 字符匹配，但 LIKE 不支持表達(dá)式的重復(fù)、復(fù)雜的更替、字符范圍、字

12、符列表和 POSIX 字符類等等。此外，新的正則表達(dá)式函數(shù)允許檢測重復(fù)出現(xiàn)的單詞和模式交換。這里的例子為您提供了正則表達(dá)式領(lǐng)域的一個概覽，以及您如何能夠在您的應(yīng)用程序中使用它們。 實實在在地豐富您的工具包 因為正則表達(dá)式有助于解決復(fù)雜的問題，所以它們是非常強(qiáng)大的。正則表達(dá)式的一些功能難于用傳統(tǒng)的 SQL 函數(shù)來仿效。當(dāng)您了解了這種稍顯神秘的語言的基礎(chǔ)構(gòu)建程序塊時，正則表達(dá)式將成為您的工具包的不可缺少的一部分（不僅在 SQL 環(huán)境下也在其它的編程語言環(huán)境下）。為了使您的各個模式正確，雖然嘗試和錯誤有時是必須的，但正則

13、表達(dá)式的簡潔和強(qiáng)大是不容置疑的。 Alice Rischert (ar280) 是哥倫比亞大學(xué)計算機(jī)技術(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

14、60;強(qiáng)公司內(nèi)擔(dān)任數(shù)據(jù)庫設(shè)計師、DBA 和項目主管，并且她自從 Oracle version 5 起就一直使用 Oracle 產(chǎn)品。 表 1：定位元字符  元字符說明使表達(dá)式定位至一行的開頭$使表達(dá)式定位至一行的末尾 表 2：量詞或重復(fù)操作符  量詞說明*匹配 0 次或更多次?匹配 0 次或 1 次+匹配 1 次或更多次m正好匹配 m 次m,至少匹

15、配 m 次m, n至少匹配 m 次但不超過 n 次表 3：預(yù)定義的 POSIX 字符類  字符類說明:alpha:字母字符:lower:小寫字母字符:upper:大寫字母字符:digit:數(shù)字:alnum:字母數(shù)字字符:space:空白字符（禁止打?。缁剀嚪?、換行符、豎直制表符和換頁符:punct:標(biāo)點字符:cntrl:控制字符（禁止打?。?print:可打印字符表 4：表達(dá)式的替換匹配和分組  元字符說明|替換分隔替換選項，通常與分組操作符

16、 () 一起使用( )分組將子表達(dá)式分組為一個替換單元、量詞單元或后向引用單元（參見"后向引用"部分）char字符列表表示一個字符列表；一個字符列表中的大多數(shù)元字符（除字符類、 和 - 元字符之外）被理解為文字表 5：REGEXP_LIKE 操作符  語法說明REGEXP_LIKE(source_string, pattern, match_parameter)source_string 支持字符數(shù)據(jù)類型（CHAR、VARCHAR2、CLOB、N

17、CHAR、NVARCHAR2 和 NCLOB，但不包括 LONG）。pattern 參數(shù)是正則表達(dá)式的另一個名稱。match_parameter 允許可選的參數(shù)（如處理換行符、保留多行格式化以及提供對區(qū)分大小寫的控制）。表 6：REGEXP_INSTR 函數(shù)  語法說明REGEXP_INSTR(source_string, pattern, start_position, occurrence, return_option, match_parameter

18、)該函數(shù)查找 pattern ，并返回該模式的第一個位置。您可以隨意指定您想要開始搜索的 start_position。 occurrence 參數(shù)默認(rèn)為 1，除非您指定您要查找接下來出現(xiàn)的一個模式。return_option 的默認(rèn)值為 0，它返回該模式的起始位置；值為 1 則返回符合匹配條件的下一個字符的起始位置。表 7： 5 位數(shù)字加 4 位郵政編碼表達(dá)式的說明  語法說明 必須匹配的空白:digit:POSI

19、X 數(shù)字類 字符列表的結(jié)尾 5字符列表正好重復(fù)出現(xiàn) 5 次(子表達(dá)式的開頭 -一個文字連字符，因為它不是一個字符列表內(nèi)的范圍元字符字符列表的開頭 :digit:POSIX :digit:類字符列表的開頭 字符列表的結(jié)尾4字符列表正好重復(fù)出現(xiàn) 4 次 )結(jié)束圓括號，結(jié)束子表達(dá)式 ? 量詞匹配分組的子表達(dá)式 0 或 1 次，從而使得 4 位代碼可選$定位元字符，指示行尾表 8：REGEXP_

20、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 , positi

21、on,occurrence, match_parameter)該函數(shù)用一個指定的 replace_string 來替換匹配的模式，從而允許復(fù)雜的"搜索并替換"操作。表 10：后向引用元字符  元字符說明digit反斜線緊跟著一個 1 到 9 之間的數(shù)字，反斜線匹配之前的用括號括起來的第 digit 個子表達(dá)式。（注意：反斜線在正則表達(dá)式中有另一種意義，取決于上下文，它還可能表示 Escape 字符。表 11：模式交換正則表達(dá)式的說明  正則表達(dá)式項目說明(第一個子表達(dá)式的開頭 .匹配除換行符之外的任意單字符*重復(fù)操作符，匹配之前的 . 元字符 0 到 n 次)第一個子表達(dá)式的結(jié)尾；匹配結(jié)果在 1中獲取（在這個例子中，結(jié)果為 Ellen。） 必須存在的空白 (第二個子表達(dá)式的開頭 .匹配除換行符之外的任意單個字符 *重復(fù)操作符，匹配之前的 . 元字符 0 到 n 次)第二個子表達(dá)