Oracle中的正規表示式(及函式)詳解

1、概述

相信大家對正規表示式都不陌生，從linux下的命令到Java等程式語言，正規表示式無處不在，雖然我們實際使用的時候也並不一定太多，但是當我們要處理字串時，它確實是一個強大的工具。

上一篇文章（Oracle中的字串型別及相關函式詳解）中，已經介紹了一些串相關的函式，也列出了用於正規表示式的函式，本文就正式介紹他們。

本文基於Oracle 12c ，可能部分內容在較老的版本中不適用。

運算子

在介紹函式前，這裡先說明一下Oracle中正規表示式運算子及其描述。

如果不知道他們有什麼用，或者也不知道描述說的是什麼，沒關係，可以先看後面的介紹，就知道他們的含義了。

合理的使用這些運算子，配合正規表示式相關的函式，不但可以讓字串處理變得更加高效，還能大大的減少程式碼量。

2、相關函式

統一說明：
函式中pattern為正規表示式，最多可以包含512個位元組。

下面的介紹中，是不同的函式，如若引數描述字串相同，即他們的意思也是相同的（如regexp_substr函式和regexp_instr函式中的position意思都是表示開始搜尋的位置），為了內容緊湊，只在那個引數第一次出現的地方作介紹。

引數帶char的表示簡單的串型別，帶string的可以包含大物件串型別（如clob）。

慣例，“[]”表示可選引數。

2.1、REGEXP_SUBSTR

REGEXP_SUBSTR函式使用正規表示式來指定返回串的起點和終點。

語法：

    regexp_substr(source_string,pattern[,position[,occurrence[,match_parameter]]])

source_string：源串，可以是常量，也可以是某個值型別為串的列。

position：從源串開始搜尋的位置。預設為1。

occurrence：指定源串中的第幾次出現。預設值1.

match_parameter：文字量，進一步訂製搜尋，取值如下：

• ‘i’ 用於不區分大小寫的匹配。
• ‘c’ 用於區分大小寫的匹配。
• ‘n’ 允許將句點“.”作為萬用字元來匹配換行符。如果省略改引數，句點將不匹配換行符。
• ‘m’ 將源串視為多行。即將“^”和“$”分別看做源串中任意位置任意行的開始和結束，而不是看作整個源串的開始或結束。如果省略該引數，源串將被看作一行來處理。
• 如果取值不屬於上述中的某個，將會報錯。如果指定了多個互相矛盾的值，將使用最後一個值。如’ic’會被當做’c’處理。
• 省略該引數時：預設區分大小寫、句點不匹配換行符、源串被看作一行。

例1：

select regexp_substr('MY INFO: Anxpp,22，and boy','my',1,1,'i') from users; 

將返回MY，如果將match_parameter改為’c’將不返回任何內容（null）。

例2：

select regexp_substr('MY INFO: Anxpp,23，and boy','[[:digit:]]',1,2) from users;

此處會返回3。

注意這裡同時用到了“[]”和“[:digit:]”。

2.2、REGEXP_INSTR

REGEXP_INSTR函式使用正規表示式返回搜尋模式的起點和終點（整數）。如果沒有發現匹配的值，將返回0。

語法：

regexp_instr(source_string,pattern[,position[,occurrence[,return_option[,match_parameter]]]])

return_option：為0時，返回第一個字元出現的位置，與instr作用相同。為1時，返回所搜尋字元出現以後下一個字元的位置。預設為0。

例1：

select regexp_instr('MY INFO: Anxpp,23，and boy','[[:digit:]]') from users;

該例會返回16。

REGEXP_INSTR函式常常會被用到where子句中。

2.3、REGEXP_LIKE

通常使用REGEXP_LIKE進行模糊匹配。

語法：

regexp_like(source_string,pattern[match_parameter])

比如，查詢電話好嗎以666開頭的：

select name from users where regexp_like(phone,'666');

例1：

select * from users where regexp_like('MY INFO: Anxpp,23，and boy','[[:digit:]]');

此例判斷串中是否包含數字。

該函式可以使用前面介紹的所有搜尋功能作為REGEXP_LIKE搜尋的一部分，可以是非常複雜的搜尋變得簡單。

2.4、REPLACE和REGEXP_REPLACE

REPLACE函式用於替換串中的某個值。

語法：

    replace(char,search_string[,replace_string])

如果不指定replace_string，會將搜尋到的值刪除。

輸入可以是任何字元資料型別：char,varchar2,nchar,nchar,nvarchar2,clob或nclob。

例1：

select replace('MY INFO: Anxpp,23，and boy','an') from users;

返回：MY INFO: Anxpp,23，d boy

下面演示使用該函式計算某字串在源串中出現的次數：

select (length('MY INFO: Anxpp,23，and boy')-length(replace('MY INFO: Anxpp,23，and boy','an')))/length('an') from users;

REGEXP_REPLACE是REPLACE的增強版，支援正規表示式，擴充套件了一些功能。
語法：

regexp_replace(source_string,pattern[,replace_string[,position[,occurrence[,match_parameter]]]])

replace_string表示用什麼來替換source_string中與pattern匹配的部分。

occurrence為非負整數，0表示所有匹配項都被替換，為正數時替換第n次匹配。

其他引數在前面都已經介紹過了。

例1：

想象這樣一個場景：有一個分散式的爬蟲，負責抓取網頁的程式已經將資料存入資料庫，而當前負責處理的程式需要從其中讀取其中的一些資料，包括電話號碼。號碼一般是11為，前3位區號，中間4位表示交換機，再加上後面4位，格式也不能確定，我們可以試著用下面的SQL來讀取，生成我們想要的格式：

    select regexp_replace('電話：023 5868-8888 郵箱：anxppp@163.com',
    '.*([[:digit:]]{3})([^[:digit:]]{0,2})([[:digit:]]{4})([^[:digit:]]{0,2})([[:digit:]]{4}).*',
    '(\1)\3\5'
    ) phone from users;

該例返回：(023)58688888

“\1”、“\3”、“\5”分別表示第1、3、5個資料集。其他的，大家慢慢看就能看懂了。

配合where子句，可以限制要返回的行。

2.5、REGEXP_COUNT

REGEXP_COUNT函式返回在源串中出現的模式的次數，作為對REGEXP_INSTR函式的補充。

雖然COUNT是一個集合函式，操作的是行組，但是REGEXP_COUNT是單行函式，分別計算每一行。
語法：

    regexp_count(source_char,pattern[,position[,match_param]])

REGEXP_COUNT返回pattern在source_char串中出現的次數。如果未找到匹配，函式返回0。

• metch_param引數，相對於前面介紹的match_parameter引數多一個取值“x”。
• ‘x’：忽略空格字元。預設情況下，空格與自身想匹配。
• metch_param如果指定了多個互相矛盾的值，將使用最後一個值。

前面介紹了使用replace函式統計字串在源串中出現的次數，這裡可以使用REGEXP_COUNT實現，而且看起來更簡單：

select regexp_count('MY INFO: Anxpp,23，and boy','an') from users;

返回：1

此處還是使用match_param引數進行不區分大小寫搜尋：

    select regexp_count('MY INFO: Anxpp,23，and boy','an',1,'i') from users;

返回：2

3、總結

以上正規表示式相關函式的使用，主要體現在對正規表示式的掌握程度上，如果精通了正規表示式，一些非常複雜的串處理，也能用比較簡潔的程式碼完成。