Oracle 10g 數(shù)據(jù)庫內建了符合 IEEE POSIX (Portable Operating System for Unix) 標準的正則表達式。熟練使用正則表達式，可以寫出簡潔，強大的 SQL 語句。
?
正則表達式有幾個優(yōu)點優(yōu)于常見的 LIKE 操作符和 INSTR 、 SUBSTR 及 REPLACE 函數(shù)的。這些傳統(tǒng)的 SQL 函數(shù)不便于進行模式匹配。只有 LIKE 操作符通過使用 % 和 _ 字符匹配，但 LIKE 不支持表達式的重復、復雜的更替、字符范圍、字符列表和 POSIX 字符類等等。
?
元字符（ Meta Character ）：
- ^???????????? 使表達式定位至一行的開頭 ??
- $???????????? 使表達式定位至一行的末尾 ??
- *???????????? 匹配 ?0? 次或更多次 ??
- ????????????? 匹配 ?0? 次或 ?1? 次 ??
- +???????????? 匹配 ?1? 次或更多次 ??
- {m}?????????? 正好匹配 ?m? 次 ??
- {m,}????????? 至少匹配 ?m? 次 ??
- {m,?n}??????? 至少匹配 ?m? 次但不超過 ?n? 次 ??
- [:alpha:]???? 字母字符 ??
- [: lower :]???? 小寫字母字符 ??
- [: upper :]???? 大寫字母字符 ??
- [:digit:]???? 數(shù)字 ??
- [:alnum:]???? 字母數(shù)字字符 ??
- [: space :]???? 空白字符（禁止打印），如回車符、換行符、豎直制表符和換頁符 [:punct:]???? 標點字符 ??
- [:cntrl:]???? 控制字符（禁止打印） ??
- [:print:]???? 可打印字符 ?|? 分隔替換選項，通常與分組操作符 ?()? 一起使用 ??
- (?)?????????? 將子表達式分組為一個替換單元、量詞單元或后向引用單元 ??
- [char]??????? 字符列表 ?? ? ?
Oracle 10g 提供了四個 regexp function: REGEXP_LIKE , REGEXP_REPLACE , REGEXP_INSTR , REGEXP_SUBSTR 。
Sql 代碼
- REGEXP_LIKE ：比較一個字符串是否與正則表達式匹配 ??
- (srcstr,?pattern?[,?match_option]) ??
- ??
- REGEXP_INSTR ：在字符串中查找正則表達式，并且返回匹配的位置 ??
- (srcstr,?pattern?[,?position?[,?occurrence?[,?return_option?[,?match_option]]]]) ??
- ??
- REGEXP_SUBSTR ：返回與正則表達式匹配的子字符串 ??
- (srcstr,?pattern?[,?position?[,?occurrence?[,?match_option]]])? ??
- ??
- REGEXP_REPLACE ：搜索并且替換匹配的正則表達式 ??
- (srcstr,?pattern?[,?replacestr?[,?position?[,?occurrence?[,?match_option]]]])??
：比較一個字符串是否與正則表達式匹配
：在字符串中查找正則表達式，并且返回匹配的位置
：返回與正則表達式匹配的子字符串
：搜索并且替換匹配的正則表達式
?
其中各參數(shù)的含義為 :
Sql 代碼
- srcstr:???????? 被查找的字符數(shù)據(jù)。 ? ??
- pattern:??????? 正則表達式。 ??
- occurrence:???? 出現(xiàn)的次數(shù)。默認為 1 。 ??
- position:?????? 開始位置 ??
- return_option:? 默認值為 0 ，返回該模式的起始位置；值為 1 則返回符合匹配條件的下一個字符的起始位置。 ??
- replacestr:???? 用來替換匹配模式的字符串。 ??
- match_option:?? 匹配方式選項。缺省為 c 。 ??
- ???????????????c ： case ?sensitive ??
- ???????????????I ： case ?insensitive??
- ???????????????n ： (.) 匹配任何字符（包括 newline) ??
- ???????????????m ：字符串存在換行的時候被作為多行處理 ?? ? ?
下面通過一些具體的例子來說明如何使用這四個函數(shù)。首先創(chuàng)建一個測試數(shù)據(jù)表，
Sql 代碼
- SQL>?create?table?person?( ??
- ??2??first_name?varchar(20), ??
- ??3??last_name?varchar(20), ??
- ??4??email?varchar(100), ??
- ??5??zip?varchar(6)); ??
- ??
- Table?created. ??
- ??
- SQL>?insert?into?person?values?('Steven',?'Chen',?' steven@hp.com ',?'123456'); ??
- 1?row?created. ??
- ??
- SQL>?insert?into?person?values?('James',?'Li',?' jamesli@sun.com '?||?chr(10)?||?' lijames@oracle.com ',?'1b3d5f'); ??
- 1?row?created. ??
- ??
- SQL>?commit; ??
- Commit?complete. ??
- ??
- SQL>?select?*?from?person; ??
- ??
- FIRST_NAME?LAST_NAME??EMAIL????????????????ZIP ??
- ----------?----------?--------------------?------ ??
- Steven?????Chen??????? steven@hp.com ????????123456 ??
- James??????Li????????? jamesli@sun.com ??????1b3d5f ??
- ?????????????????????? lijames@oracle.com ??
?
1 。 REGEXP_LIKE
Sql 代碼
- SQL>?select?zip?as?invalid_zip?from?person?where?regexp_like(zip,?'[^[:digit:]]'); ??
- INVALID_ZIP ??
- -------------------- ??
- 1b3d5f ??
- ??
- SQL>?select?first_name?from?person?where?regexp_like(first_name,?'^S.*n$'); ??
- FIRST_NAME ??
- ---------- ??
- Steven ??
- ??
- SQL>?select?first_name?from?person?where?regexp_like(first_name,?'^s.*n$'); ??
- no?rows?selected ??
- ??
- SQL>?select?first_name?from?person?where?regexp_like(first_name,?'^s.*n$',?'c'); ??
- no?rows?selected ??
- ??
- SQL>?select?first_name?from?person?where?regexp_like(first_name,?'^s.*n$',?'i'); ??
- FIRST_NAME ??
- ---------- ??
- Steven ??
- ??
- SQL>?select?email?from?person?where?regexp_like(email,?'^james.*com$'); ??
- no?rows?selected ??
- ??
- SQL>?select?email?from?person?where?regexp_like(email,?'^james.*com$',?'n'); ??
- EMAIL ??
- -------------------- ??
- jamesli@sun.com ??
- lijames@oracle.com ??
- ??
- SQL>?select?email?from?person?where?regexp_like(email,?'^li.*com$'); ??
- no?rows?selected ??
- ??
- SQL>?select?email?from?person?where?regexp_like(email,?'^li.*com$',?'m'); ??
- EMAIL ??
- -------------------- ??
- jamesli@sun.com ??
- lijames@oracle.com ??
?
注意上面分別測試了不同的 match_option 對結果的影響。
?
2 。 REGEXP_INSTR
Sql 代碼
- 查找 zip 中第一個非數(shù)字字符的位置 ??
- SQL>?select?regexp_instr(zip,?'[^[:digit:]]')?as?position?from?person; ??
- ??POSITION ??
- ---------- ??
- ?????????0 ??
- ?????????2 ??
- ??
- 從第三個字符開始，查找 zip 中第二個非數(shù)字字符的位置 ??
- SQL>?select?regexp_instr(zip,?'[^[:digit:]]',?3,?2)?as?position?from?person; ??
- ??POSITION ??
- ---------- ??
- ?????????0 ??
- ?????????6 ??
- ??
- 從第三個字符開始，查找 zip 中第二個非數(shù)字字符的下一個字符位置 ??
- SQL>?select?regexp_instr(zip,?'[^[:digit:]]',?3,?2,?1)?as?position?from?person; ??
- ??POSITION ??
- ---------- ??
- ?????????0 ??
- ?????????7??
查找 中第一個非數(shù)字字符的位置
從第三個字符開始，查找 中第二個非數(shù)字字符的位置
從第三個字符開始，查找 中第二個非數(shù)字字符的下一個字符位置
?
3 。 REGEXP_SUBSTR
Sql 代碼
- SQL>?select?regexp_substr(zip,?'[^[:digit:]]')?as?zip?from?person; ??
- ZIP ??
- ------------------ ??
- b ??
- ??
- SQL>?select?regexp_substr(zip,?'[^[:digit:]]',?3,?2)?as?zip?from?person; ??
- ZIP ??
- ------------ ??
- f??
?
4 。 REGEXP_REPLACE
Sql 代碼
- 把 zip 中所有非數(shù)字字符替換為 0 ??
- SQL>?update?person?set?zip=regexp_replace(zip,?'[^[:digit:]]',?'0') ??
- ??2??where?regexp_like(zip,?'[^[:digit:]]'); ??
- 1?row?updated. ??
- ??
- SQL>?select?zip?from?person; ??
- ZIP ??
- ------ ??
- 123456 ??
- 103050??
把 中所有非數(shù)字字符替換為
?
后向引用（ backreference ）：
?
后向引用是 一個很有用的特性。它能夠把子表達式的匹配部分保存在臨時緩沖區(qū)中，供以后重用 。緩沖區(qū)從左至右進行編號，并利用 \digit 符號進行訪問。子表達式用一組圓括號來顯示。利用后向引用可以實現(xiàn)較復雜的替換功能。
- SQL>?select?regexp_replace('Steven?Chen',?'(.*)?(.*)',?'\2,?\1')?as?reversed_name?from?dual; ??
- ??
- REVERSED_NAME ??
- -------------------- ??
- Chen,?Steven??
?
在 DDL 中也可以正則表達式，比如 Constraint, index, view
Sql 代碼
- SQL>?alter?table?person?add?constraint?constraint_zip?check?(regexp_like(zip,?'^[[:digit:]]+$')); ??
- ??
- SQL>?create?index?person_idx?on?person(regexp_substr(last_name,?'^[[:upper:]]'));??
一. 匹配字符 ??
|
字符類 |
匹配的字符 |
舉 例 |
|
d |
從０ - ９的任一數(shù)字 |
dd 匹配 72, 但不匹配 aa 或 7a |
|
D |
任一非數(shù)字字符 |
DDD 匹配 abc, 但不匹配 123 |
|
w |
任一單詞字符，包括 A-Z,a-z,0-9 和下劃線 |
wwww 匹配 Ab-2 ，但不匹配 ∑ ￡$%* 或 Ab_ @ |
|
W |
任一非單詞字符 |
W 匹配＠，但不匹配 a |
|
s |
任一空白字符，包括制表符，換行符，回車符，換頁符和垂直制表符 |
匹配在 HTML,XML 和 其他 標準定義中的所有傳統(tǒng)空白字符 |
|
S |
任一非空白字符 |
空白字符以外的任意字符 , 如 A% &g3; 等 |
|
. |
任一字符 |
匹配除換行符以外的任意字符除非設置了 MultiLine 先項 |
|
[…] |
括號中的任一字符 |
[abc] 將匹配一個單字符 ,a,b 或 c. [a-z] 將匹配從 a 到 z 的任一字符 |
|
[^…] |
不在括號中的任一字符 |
[^abc] 將匹配一個 a 、 b 、 c 之外的單字符 , 可以 a,b 或 A 、 B 、 C [a-z] 將匹配不屬于 a-z 的任一字符 , 但可以匹配所有的大寫字母 |
二. 重復字符
|
重復字符 |
含 義 |
舉 例 |
|
｛ n ｝ |
匹配前面的字符 n 次 |
x{2} 匹配 xx, 但不匹配 x 或 xxx |
|
｛ n, ｝ |
匹配前面的字符至少 n 次 |
x{2} 匹配 2 個或更多的 x, 如 xxx,xxx. . |
|
｛ n,m ｝ |
匹配前面的字符至少 n 次 , 至多 m 次。如果 n 為 0 ，此參數(shù)為可選參數(shù) |
x{2,4} 匹配 xx,xxx,xxxx, 但不匹配 xxxxx |
|
? |
匹配前面的字符 0 次或 1 次，實質上也是可選的 |
x? 匹配 x 或零個 x |
|
+ |
匹配前面的字符 0 次或多次 |
x+ 匹配 x 或 xx 或大于 0 的任意多個 x |
|
* |
匹配前面的字符 0 次或更多次 |
x* 匹配 0,1 或更多個 x |
三 . 定位字符
|
定位字符 |
描 述 |
|
^ |
隨后的模式必須位于字符串的開始位置，如果是一個多行字符串，則必須位于行首。對于多行文本（包含回車符的一個字符串）來說，需要設置多行標志 |
|
$ |
前面的模式必須位于字符串的未端，如果是一個多行字符串，必須位于行尾 |
|
A |
前面的模式必須位于字符串的開始位置，忽略多行標志 |
|
z |
前面的模式必須位于字符串的未端，忽略多行標志 |
|
Z |
前面的模式必須位于字符串的未端，或者位于一個換行符前 |
|
b |
匹配一個單詞邊界，也就是一個單詞字符和非單詞字符中間的點。要記住一個單詞字符是 [a-zA-Z0-9] 中的一個字符。位于一個單詞的詞首 |
|
B |
匹配一個非單詞字符邊界位置，不是一個單詞的詞首 |
分組字符
|
分組字符 |
定 義 |
舉 例 |
|
（） |
此字符可以組合括號內模式所匹配的字符，它是一個捕獲組，也就是說模式匹配的字符作為最終設置了 ExplicitCapture 選項 ―― 默認狀態(tài)下字符不是匹配的一部分 |
輸入字符串為： ABC1DEF2X Y 匹配 3 個從 A 到 Z 的字符和 1 個數(shù)字的正則表達式：（ [A-Z]{3}d ） 將產生兩次匹配： Match 1=ABC1;Match 2=DEF2 每次匹配對應一個組： Match1 的第一個組＝ ABC;Match2 的第 1 個組＝ DEF 有了反向引用，就可以通過它在正則表達式中的編號以及 C# 和類 Group,GroupCollection 來訪問組。如果設置了 ExplicitCapture 選項，就不能使用組所捕獲的內容 |
|
（ ?: ） |
此字符可以組合括號內模式所匹配的字符，它是一個非捕獲組，這意味著模式所的字符將不作為一個組來捕獲，但它構成了最終匹配結果的一部分。它基本上與上面的組類型相同，但設定了選項 ExplicitCapture |
輸入字符串為： 1A BB SA1 C 匹配一個數(shù)字或一個 A 到 Z 的字母，接著是任意單詞字符的正則表達式為：（ ?:d|[A-Z]w ） 它將產生 3 次匹配：每 1 次匹配＝ 1A ；每 2 次匹配＝ BB; 每 3 次匹配＝ SA 但是沒有組被捕獲 |
|
（ ?<name> ） |
此選項組合括號內模式所匹配的字符，并用尖括號中指定的值為組命名。在正則表達式中，可以使用名稱進行反向引用，而不必使用編號。即使不設置 ExplicitCapture 選項，它也是一個捕獲組。這意味著反向引用可以利用組內匹配的字符，或者通過 Group 類訪問 |
輸入字符串為： Characters in Sienfeld included Jerry Seinfeld,Elaine Benes,Cosno Kramer and George Costanza 能夠匹配它們的姓名，并在一個組 llastName 中捕獲姓的正則表達式為： b[A-Z][a-z]+(?<lastName>[A-Z][a-z]+)b 它產生了 4 次匹配： First Match=Jerry Seinfeld; Second Match=Elaine Benes; Third Match=Cosmo Kramer; Fourth Match=George Costanza 每一次匹配都對應了一個 lastName 組： 第 1 次匹配： lastName group=Seinfeld 第 2 次匹配： lastName group=Benes 第 3 次匹配： lastName group=Kramer 第 4 次匹配： lastName group=Costanza 不管是否設置了選項 ExplictCapture ，組都將被捕獲 |
|
（ ?= ） |
正聲明。聲明的右側必須是括號中指定的模式。此模式不構成最終匹配的一部分 |
正則表達式 S+(?=.NET) 要匹配的輸入字符串為： The languages were Java,C#.NET,VB.NET,C,Jscript.NET,Pascal 將產生如下匹配： 〕 C# VB JScript. |
|
（ ?! ） |
負聲明。它規(guī)定模式不能緊臨著聲明的右側。此模式不構成最終匹配的一部分 |
d{3}(?![A-Z]) 要匹配的輸入字符串為： 123A 456 789111C 將產生如下匹配： 456 789 |
|
（ ? <= ） |
反向正聲明。聲明的左側必須為括號內的指定模式。此模式不構成最終匹配的一部分 |
正則表達式 (?<=New)([A-Z][a-z]+) 要匹配的輸入字符串為： The following states,New Mexico,West Virginia,Washington,New England 它將產生如下匹配： Mexico England |
|
（ ? <! ） |
反向正聲明。聲明的左側必須不能是括號內的指定模式。此模式不構成最終匹配的一部分 |
正則表達式 (?<!1)d{2}([A-Z]) 要匹配的輸入字符串如下： 123A456F789C111A 它將實現(xiàn)如下匹配： 56F 89C |
|
（ ?> ） |
非回溯組。防止 Regex 引擎回溯并且防止實現(xiàn)一次匹配 |
假設要匹配所有以 “ ing” 結尾的單詞。輸入字符串如下： He was very trusin g 正則表達式為： .*ing 它將實現(xiàn)一次匹配 ―― 單詞 trusting 。 “ .” 匹配任意字符，當然也匹配 “ ing” 。所以， Regex 引擎回溯一位并在第 2 個 “t” 停止，然后匹配指定的模式 “ ing” 。但是，如果禁用回溯操作： (? >.*)ing 它將實現(xiàn) 0 次匹配。 “.” 能匹配所有的字符，包括 “ ing”―― 不能匹配，從而匹配失敗 |
決策字符
|
字 符 |
描 述 |
舉 例 |
|
（ ?(regex)yes_regex|no_regex ） |
如果表達式 regex 匹配，那么將試圖匹配表達式 yes 。否則匹配表達式 no 。正則表達式 no 是可先參數(shù)。注意，作出決策的模式寬度為 0. 這意味著表達式 yes 或 no 將從與 rege x 表達式相同的位置開始匹配 |
正則表達式 (?(d)dA|A-Z)B) 要匹配的輸入字符串為： 1A CB3A5C 3B 它實現(xiàn)的匹配是： 1A CB 3A |
|
（ ?(group name or number)yes_regex|no_regex ） |
如果組中的正則表達式實現(xiàn)了匹配，那么試圖匹配 yes 正則表達式。否則，試圖匹配正則表達式 no 。 no 是可先的參數(shù) |
正則表達式 (d7)?-(?(1)dd[A-Z]|[A-Z][A-Z] 要匹配的輸入字符串為： 77-77A 69-AA 57-B 它實現(xiàn)的匹配為： 77-77A － AA |
替換字符
|
字 符 |
描 述 |
|
$group |
用 group 指定的組號替換 |
|
${name} |
替換被一個 (? <name>) 組匹配的最后子串 |
|
$$ |
替換一個字符 $ |
|
$& |
替換整個的匹配 |
|
$^ |
替換輸入字符串匹配之前的所有文本 |
|
$’ |
替換輸入字符串匹配之后的所有文本 |
|
$+ |
替換最后捕獲的組 |
|
$_ |
替換整個的輸入字符串 |
轉義序列
|
字 符 |
描 述 |
| ? |
匹配字符 “ ” |
|
. |
匹配字符 “. ” |
|
* |
匹配字符 “ *” |
|
+ |
匹配字符 “ +” |
|
? |
匹配字符 “? ” |
|
| |
匹配字符 “| ” |
|
( |
匹配字符 “ (” |
|
) |
匹配字符 “ )” |
|
{ |
匹配字符 “{ ” |
|
} |
匹配字符 “} ” |
|
^ |
匹配字符 “^ ” |
|
$ |
匹配字符 “$ ” |
|
n |
匹配換行符 |
|
r |
匹配回車符 |
|
t |
匹配制表符 |
|
v |
匹配垂直制表符 |
|
f |
匹配換面符 |
|
nnn |
匹配一個 8 進數(shù)字， nnn 指定的 ASCII 字符。如 103 匹配大寫的 C |
|
xnn |
匹配一個 16 進數(shù)字， nn 指定的 ASCII 字符。如 x43 匹配大寫的 C |
|
unnnn |
匹配由 4 位 16 進數(shù)字（由 nnnn 表示）指定的 Unicode 字符 |
|
cV |
匹配一個控制字符，如 cV 匹配 Ctrl- V |
選項標志
|
選項標志 |
名 稱 |
|
I |
IgnoreCase |
|
M |
Multiline |
|
N |
ExplicitCapture |
|
S |
SingleLine |
|
X |
IgnorePatternWhitespace |
選項本身的信作含義如下表所示：
|
標 志 |
名 稱 |
|
IgnoreCase |
使模式匹配不區(qū)分大小寫。默認的選項是匹配區(qū)分大小寫 |
|
RightToLeft |
從右到左搜索輸入字符串。默認是從左到右以符合英語等的閱讀習慣，但不符合阿拉伯語或希伯來語的閱讀習慣 |
|
None |
不設置標志。這是默認選項 |
|
Multiline |
指定 ^ 和 $ 可以匹配行首和行尾，以及字符串的開始和結尾。這意味著可以匹配每個用換行符分隔的行。但是，字符 “.” 仍然不匹配換行符 |
|
SingleLine |
規(guī)定特殊字符 “.” 匹配任意的字符，包括換行符。默認情況下，特殊字符 “.” 不匹配換行符。通常與 MultiLine 選項一起使用 |
|
ECMAScript. |
ECMA(European Coputer Manufacturer’s Association, 歐洲計算機生產商協(xié)會 ) 已經(jīng)定義了正則表達式應該如何實現(xiàn)，而且已經(jīng)在 ECMAScript 規(guī)范中實現(xiàn)，這是一個基于標準的 JavaScript 。這個選項只能與 IgnoreCase 和 MultiLine 標志一起使用。與其它任何標志一起使用， ECMAScript 都將產生異常 |
|
IgnorePatternWhitespace |
此選項從使用的正則表達式模式中刪除所有非轉義空白字符。它使表達式能跨越多行文本，但必須確保對模式中所有的空白進行轉義。如果設置了此選項，還可以使用 “#” 字符來注釋下則表達式 |
|
Complied |
它把正則表達式編譯為更接近機器代碼的代碼。這樣速度快，但不允許對它進行任何修改 |
更多文章、技術交流、商務合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
