引言

正則表達(dá)式 (Regular expression) 是一種強(qiáng)大的文本處理工具，Oracle數(shù)據(jù)庫自9i版本開始引入了正則表達(dá)式支持，可幫助開發(fā)者快速而準(zhǔn)確地匹配、查找和替換字符串，廣泛應(yīng)用于數(shù)據(jù)查詢、數(shù)據(jù)分析、數(shù)據(jù)清洗等領(lǐng)域。

1. 基本語法

Oracle正則表達(dá)式的語法基于Perl語言的正則表達(dá)式語法，區(qū)分大小寫（case sensitive）。

（1）字符匹配

• .：匹配除了換行外的任意一個(gè)字符；
• \d：匹配任何數(shù)字，相當(dāng)于 [0-9]；
• \D：匹配任何非數(shù)字字符，相當(dāng)于 [^0-9]；
• \w：匹配任何字母數(shù)字字符或下劃線，相當(dāng)于 [a-zA-Z0-9_]；
• \W：表示匹配任何非字母數(shù)字字符或下劃線，相當(dāng)于 [^a-zA-Z0-9_]。

（2）限定符

• *：匹配前一個(gè)字符出現(xiàn)0次或多次；
• +：匹配前一個(gè)字符出現(xiàn)1次或多次；
• ?：匹配前一個(gè)字符出現(xiàn)0次或1次；
• {n}：匹配前一個(gè)字符出現(xiàn)n次；
• {n,}：匹配前一個(gè)字符出現(xiàn)n次或更多；
• {n,m}：匹配前一個(gè)字符出現(xiàn)n~m次。

'|' ：指兩項(xiàng)之間的一個(gè)選擇。

e.g.

^([a-z]+|[0-9]+)$：表示所有小寫字母或數(shù)字組合成的字符串。

（3）邊界匹配

• ^：匹配開始位置；
• $：匹配結(jié)束位置；
• \b：匹配單詞邊界，即單詞的開頭或結(jié)尾位置；
• \B：匹配非單詞邊界，即不是單詞的開頭或結(jié)尾位置。

（4）分組和引用

• ()：分組，標(biāo)記一個(gè)子表達(dá)式的開始和結(jié)束位置；
• \num：引用第num個(gè)子表達(dá)式，num從1開始。

（5）字符集合

• []：表示一組字符中的任意一個(gè)。

（6）轉(zhuǎn)義符

• \\：表示轉(zhuǎn)義一個(gè)字符。

oracle正則表達(dá)式還支持一些高級(jí)語法，例如貪婪匹配、非貪婪匹配、零寬斷言(zero-width assertion)、后向引用(backreference)、捕獲組等。

2. POSIX字符類

Oracle數(shù)據(jù)庫中的POSIX字符類是一組特殊的字符類，用于在正則表達(dá)式中匹配特定的字符。

POSIX字符類以 [: 開頭，以 :] 結(jié)尾，中間包含一個(gè)或多個(gè)字符，代表特定的字符集合。POSIX字符類中的字符集合可以是預(yù)定義的，也可以是自定義的。

• [[:alpha:]] 任何字母，等同于字符集合 [a-zA-Z]；
• [[:digit:]] 任何數(shù)字，等同于字符集合 [0-9]；
• [[:alnum:]] 任何字母和數(shù)字，等同于字符集合 [a-zA-Z0-9]；
• [[:space:]] 任何白字符；
• [[:upper:]] 任何大寫字母；
• [[:lower:]] 任何小寫字母；
• [[:punct:]] 任何標(biāo)點(diǎn)符號(hào)；
• [[:xdigit:]] 任何16進(jìn)制的數(shù)字，相當(dāng)于[0-9a-fA-F]。

Oracle數(shù)據(jù)庫中，POSIX字符類可以用于各種正則表達(dá)式相關(guān)的操作，如模式匹配、替換、分割等。由于Oracle數(shù)據(jù)庫中的POSIX字符類與其他數(shù)據(jù)庫或編程語言中的POSIX字符類可能略有不同，具體使用時(shí)需要查看相關(guān)文檔。

3. 正則表達(dá)式函數(shù)

Oracle數(shù)據(jù)庫提供了多種正則表達(dá)式函數(shù)，可以對(duì)文本數(shù)據(jù)進(jìn)行匹配、替換等操作。

• REGEXP_LIKE: 判斷字符串是否匹配指定的正則表達(dá)式。

e.g. 查詢員工名字以"S"開頭，以"n"結(jié)尾的記錄：

SELECT * FROM emp WHERE REGEXP_LIKE(emp_name, '^S.*n$');

• REGEXP_REPLACE: 替換字符串中的子串。

e.g. 將字符串"12345"中連續(xù)的三個(gè)數(shù)字替換成星號(hào)"*"，輸出
“*45”：

SELECT REGEXP_REPLACE('12345', '\d{3}', '*') FROM dual;

• REGEXP_SUBSTR: 提取字符串中匹配指定正則表達(dá)式的子串。

e.g. 從字符串"abc 123 def"中提取出連續(xù)的數(shù)字"123"：

SELECT REGEXP_SUBSTR('abc 123 def', '\d+') FROM dual;

• REGEXP_INSTR: 返回字符串中匹配指定正則表達(dá)式的子串的位置。

e.g. 返回字符串"1ab2cd3ef"中第一個(gè)連續(xù)數(shù)字的起始位置，即1

SELECT REGEXP_INSTR('1ab2cd3ef', '\d+') FROM dual;

4. 常用正則表達(dá)式

-- 查詢value中不是純數(shù)字的記錄
select * from employee where not regexp_like(value,'^[[:digit:]]+$');

-- 查詢value中不包含任何數(shù)字的記錄。
select * from employee where regexp_like(value,'^[^[:digit:]]+$');

--查詢所有包含小寫字母或者數(shù)字的記錄。
select * from employee where regexp_like(value,'^([a-z]+|[0-9]+)$');

-- 提取字符串中的數(shù)字
create or replace function EXTRACT_NUMBER(STR in varchar2) return varchar2 is
  POSITION number;
  STR_EXT varchar2(800);
  STR_TMP varchar2(800);
  result  varchar2(800);
begin
  STR_EXT:=STR;
  POSITION:=1;
  LOOP
    STR_TMP:=REGEXP_SUBSTR(STR_EXT,'([0-9]+)',POSITION);
    result:=result||STR_TMP;
    STR_EXT:= SUBSTR(STR_EXT,POSITION+LENGTH(STR_TMP),LENGTH(STR));
    POSITION:=regexp_instr(STR_EXT,'([0-9]+)',1);
  exit when POSITION is null or POSITION=0;
  end loop;
  return(result);
end EXTRACT_NUMBER;

附：Oracle使用正則表達(dá)式匹配/修改記錄

1、匹配數(shù)字+符號(hào)

場(chǎng)景：有N行記錄，均帶有數(shù)字和頓號(hào)前綴，需要篩選以上格式的內(nèi)容行

正則：^[0-9][:、]

語法：regexp(原內(nèi)容，正則表達(dá)式)

SQL示例：select * from table where regexp_like(source_character,'^[0-9][:、]')

類似于:

select * from table where source_character like '數(shù)字、%'

2、修改匹配到的內(nèi)容，并提交事務(wù)

場(chǎng)景：有N條記錄，需要對(duì)其中M條具有固定格式的記錄進(jìn)行批量更新。更新規(guī)則為：去掉數(shù)字加頓號(hào)的序號(hào)前綴

正則：使用regexp_replace移除字符串前的數(shù)字+符號(hào)

語法：regexp_replace(原內(nèi)容,正則表達(dá)式,替換后的內(nèi)容，替換第N次出現(xiàn)的字符)

示例：update table set col = regexp_replace(col,'^[0-9][:、]'，'',1) where xxx

解釋：匹配所有以數(shù)字+、開頭的字符串，并替換為空白，且僅替換第一次匹配到的內(nèi)容

總結(jié) 

到此這篇關(guān)于Oracle正則表達(dá)式詳解的文章就介紹到這了,更多相關(guān)Oracle正則表達(dá)式內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論