Oracle正規表示式學習

Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE本文參考Oracle官方網站的相關文件，並加了一些實用例子

 

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

使用正規表示式編寫更好的 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）。因此， 正規表示式 ^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

----------

Joe Smith

 

'^' 匹配輸入字串的開始位置，在方括號表示式中使用，此時它表示不接受該字符集合。

'$' 匹配輸入字串的結尾位置。如果設定了 RegExp 物件的 Multiline 屬性，則 $ 也匹配 'n' 或 'r'。

'.' 匹配除換行符 n之外的任何單字元。

'?' 匹配前面的子表示式零次或一次。

'*' 匹配前面的子表示式零次或多次。

'+' 匹配前面的子表示式一次或多次。

'( )' 標記一個子表示式的開始和結束位置。

'[]' 標記一箇中括號表示式。

'{m,n}' 一個精確地出現次數範圍，m=<出現次數<=n，'{m}'表示出現m次，'{m,}'表示至少出現m次。

'|' 指明兩項之間的一個選擇。例子'^([a-z]+|[0-9]+)$'表示所有小寫字母或數字組合成的字串。

num 匹配 num，其中 num 是一個正整數。對所獲取的匹配的引用。

 

create table TEST

(

  MC VARCHAR2(60)

);

insert into TEST (MC) values ('b0');

insert into TEST (MC) values ('0b');

insert into TEST (MC) values ('1234-233-3223-2323');

insert into TEST (MC) values ('123-45-5678');

insert into TEST (MC) values ('123-56-1234567890');

insert into TEST (MC) values ('123456789');

insert into TEST (MC) values ('idadfa');

insert into TEST (MC) values ('[a');

insert into TEST (MC) values ('[i');

insert into TEST (MC) values ('[a-c]');

insert into TEST (MC) values ('[a-c]a');

insert into TEST (MC) values ('a[a-c]');

insert into TEST (MC) values ('[bdd-a]');

insert into TEST (MC) values ('[adddddd');

insert into TEST (MC) values ('[eeeea]');

insert into TEST (MC) values ('[eeeee]');

insert into TEST (MC) values ('[b]');

insert into TEST (MC) values ('112233445566778899');

insert into TEST (MC) values ('22113344 5566778899');

insert into TEST (MC) values ('991122334455667788');

insert into TEST (MC) values ('aabbccddee');

insert into TEST (MC) values ('bbaaaccddee');

insert into TEST (MC) values ('eeaabbccdd');

insert into TEST (MC) values ('ab123');

insert into TEST (MC) values ('123xy');

insert into TEST (MC) values ('007ab');

insert into TEST (MC) values ('abcxy');

insert into TEST (MC) values ('The final test is is is how to find duplicate words.');

commit;

select 1,'^[:digit:]',mc from test where regexp_like(mc,'^[:digit:]')  --以':digit'中的任何一個字元開頭的字串

union

select 2,'[^[:digit:]]',mc from test where regexp_like(mc,'[^[:digit:]]') --任何含有非數字的字元列表

union

select 3,'^[[:digit:]]',mc from test where regexp_like(mc,'^[[:digit:]]') --數字開頭

union

select 4,'^[^[:digit:]]',mc from test where regexp_like(mc,'^[^[:digit:]]') --包含任何非數字開頭的

union

select 5,'[[:digit:]]',mc from test where regexp_like(mc,'[[:digit:]]') --任何含有數字的字元列表

 

 

create table email

( email varchar2(100)

)

insert into email values('windboy@vip.sina.com');

insert into email values('windboy@sina.com');

insert into email values('window2007@vip.sina.com');

insert into email values('21com@sina.com');

insert into email values('windboy@163.com');

insert into email values('test@mail.vip.sina.com');

insert into email values('test.mail.vip.sina.com');

insert into email values('test');

commit;

select * from email where REGEXP_LIKE(email,'^[[:alnum:]];

 

 

關於正規表示式的後向引用，暫時還是沒法理解

select 0,REGEXP_REPLACE('Ellen Hildi Smith','(.*) (.*) (.*)', '\11\22\33') from dual

union

select 1,REGEXP_REPLACE('Ellen Hildi Smith','(.*)(.*)(.*)', '\11\22\33') from dual  --2，3

union

select 2,REGEXP_REPLACE('Ellen Hildi Smith','(.*)(.*) (.*)', '\11\22\33') from dual  --2

union

select 3,REGEXP_REPLACE('Ellen Hildi Smith','(.*) (.*)(.*)', '\11\22\33') from dual  --3

union

select 4,REGEXP_REPLACE('EllenHildi Smith','(.*) (.*) (.*)', '\11\22\33') from dual

union

select 5,REGEXP_REPLACE('EllenHildi Smith','(.*)(.*)(.*)', '\11\22\33') from dual  --2，3

union

select 6,REGEXP_REPLACE('EllenHildi Smith','(.*)(.*) (.*)', '\11\22\33') from dual  --2

union

select 7,REGEXP_REPLACE('EllenHildi Smith','(.*) (.*)(.*)', '\11\22\33') from dual  --3

union

select 8,REGEXP_REPLACE('Ellen HildiSmith','(.*) (.*) (.*)', '\11\22\33') from dual

union

select 9,REGEXP_REPLACE('Ellen HildiSmith','(.*)(.*)(.*)', '\11\22\33') from dual  --2，3

union

select 10,REGEXP_REPLACE('Ellen HildiSmith','(.*)(.*) (.*)', '\11\22\33') from dual  --2

union

select 11,REGEXP_REPLACE('Ellen HildiSmith','(.*) (.*)(.*)', '\11\22\33') from dual  --3