oracleRegex函數 匹配 手機

ORACLE中的支援Regex的函數主要有下面四個：

1，REGEXP_LIKE ：與LIKE的功能相似

2，REGEXP_INSTR ：與INSTR的功能相似

3，REGEXP_SUBSTR ：與SUBSTR的功能相似

4，REGEXP_REPLACE ：與REPLACE的功能相似

1、Regex中的元字元
元字元 意思 例子
說明要匹配的字元是一個特殊字元、常量或者後者引用。（後引用重複上一次的匹配） n 匹配分行符號
\ 匹配
( 匹配 (
) 匹配 )
^ 匹配字串的開頭位置 如果A是字串的第一個字元，^A 匹配 A
$ 匹配字串的末尾位置 如果B是字串的最後一個字元，$B 匹配 B
* 匹配前面的字元0次或多次 ba*rk可以匹配 brk、bark、baark等等
+ 匹配前面的字元1次或多次 ba+rk可以匹配 bark、baark等等,但是不能匹配brk，也就是說，最少有以一次。
? 匹配前面的字元0次或1次 ba?rk可以匹配 bark、brk等等,但是不能匹配baark。
{n} 匹配前面的字元恰好是n次，其中n是整數 hob{2}it可以匹配hobbit
{n,m} 匹配前面的字元至少是n次，最多是m次，其中n，m都是整數 hob{2,3}it可以匹配hobbit或者hobbbit
. 匹配除null以外的任意單個字元 hob.it中的.可以是任意的單個字元，如：hobsit等等
(pattern) 括弧中pattern是一個子Regex，匹配指定pattern模式的一個子運算式。 如：aaa(x|y)可以匹配aaax或者aaay。
x|y 匹配“或” x|y可以匹配x或者y
[abc] 可以匹配abc中的任何單個字元 hello[abc]可以匹配helloa,hellob,helloc
[a-z] 可以匹配指定範圍內的任何單個字元 hell[a-z]可以匹配hello或者hellz
[::] 指定一個字元類，可以匹配該類中的任何字元 [:alphanum:]可以匹配字元0-9、A-Z、a-z
[:alpha:]可以匹配字元A-Z、a-z
[:blank:]可以匹配空格或tab鍵
[:digit:]可以匹配數字0-9
[:graph:]可以匹配非Null 字元
[:lower:]可以匹配小寫字母a-z
[:print:]與[:graph:]類似，不同之處在於[:print:]包括空白字元
[:punct:]可以匹配標點符號.,""等等
[:space:]可以匹配所有的Null 字元
[:upper:]可以匹配大寫字母A-Z
[:xdigit:]可以匹配十六進位數字0-9、A-F、a-f
n 這是對前一次匹配命中的一個後引用，其中n是一個正整數 (.)1可以匹配兩個連續相同的非Null 字元。(.)可以匹配除null以外的任何單個字元，而1則重複上一次匹配的內容，即再次匹配相同的字元，因此可以匹配兩個連續相同的非Null 字元

2、REGEXP_LIKE(x,pattern[,match_option])用於在x中尋找Regexpattern，該函數還可以提供一個可選的參數match_option字串說明預設的匹配選項。match_option的取值如下：
 ‘c’   說明在進行匹配時區分大小寫（預設值）；
  'i'   說明在進行匹配時不區分大小寫；
  'n'   允許使用可以匹配任一字元的操作符；
  'm'   將x作為一個包含多行的字串。

--測試資料

代碼如下

複製代碼

create table test(mc varchar2(60)); insert into test values('112233445566778899'); insert into test values('22113344 5566778899'); insert into test values('33112244 5566778899'); insert into test values('44112233 5566 778899'); insert into test values('5511 2233 4466778899'); insert into test values('661122334455778899'); insert into test values('771122334455668899'); insert into test values('881122334455667799'); insert into test values('991122334455667788'); insert into test values('aabbccddee'); insert into test values('bbaaaccddee'); insert into test values('ccabbddee'); insert into test values('ddaabbccee'); insert into test values('eeaabbccdd'); insert into test values('ab123'); insert into test values('123xy'); insert into test values('007ab'); insert into test values('abcxy'); insert into test values('The final test is is is how to find duplicate words.'); commit;

一、REGEXP_LIKE

代碼如下	複製代碼
select * from test where regexp_like(mc,'^a{1,3}'); select * from test where regexp_like(mc,'a{1,3}'); select * from test where regexp_like(mc,'^a.*e$'); select * from test where regexp_like(mc,'^[[:lower:]]|[[:digit:]]'); select * from test where regexp_like(mc,'^[[:lower:]]'); Select mc FROM test Where REGEXP_LIKE(mc,'[^[:digit:]]'); Select mc FROM test Where REGEXP_LIKE(mc,'^[^[:digit:]]');

二、REGEXP_INSTR

代碼如下	複製代碼
Select REGEXP_INSTR(mc,'[[:digit:]]$') from test; Select REGEXP_INSTR(mc,'[[:digit:]]+$') from test; Select REGEXP_INSTR('The price is $400.','$[[:digit:]]+') FROM DUAL; Select REGEXP_INSTR('onetwothree','[^[[:lower:]]]') FROM DUAL; Select REGEXP_INSTR(',,,,,','[^,]*') FROM DUAL; Select REGEXP_INSTR(',,,,,','[^,]') FROM DUAL;

三、REGEXP_SUBSTR

代碼如下	複製代碼
SELECT REGEXP_SUBSTR(mc,'[a-z]+') FROM test; SELECT REGEXP_SUBSTR(mc,'[0-9]+') FROM test; SELECT REGEXP_SUBSTR('aababcde','^a.*b') FROM DUAL;

四、REGEXP_REPLACE

代碼如下	複製代碼
Select REGEXP_REPLACE('Joe Smith','( ){2,}', ',') AS RX_REPLACE FROM dual; Select REGEXP_REPLACE('aa bb cc','(.*) (.*) (.*)', '3, 2, 1') FROM dual； SQL> select * from test; ID MC -------------------- ------------------------------------------------------------ A AAAAA a aaaaa b bbbbb

SQL> select * from test where regexp_like(id,'b','i'); --不區分資料大小寫

ID MC
-------------------- ------------------------------------------------------------

b bbbbb

#End

接下來的幾節將會介紹更多有關Regex函數的知識。

1. REGEXP_LIKE()
REGEXP_LIKE(x, pattern [, match_option])用於在x中尋找pattern參數中定義的Regex，該函數還可以提供一個選擇性參數match_option，它可以設定為下面幾個字元之一：

'c'，說明在匹配時區分大小寫(預設選項)
'I'，說明在匹配時不區分大小寫
'n'，允許使用可以匹配任一字元的操作符
'm'，將x 作為一個包含多行的字串
下面這個查詢使用REGEXP_LIKE函數檢索生日在1965年到1968年之間的顧客：

代碼如下	複製代碼
SELECT customer_id, first_name, last_name, dob FROM customers WHERE REGEXP_LIKE(TO_CHAR(dob, 'YYYY'), '^196[5-8]$');   CUSTOMER_ID FIRST_NAME LAST_NAME    DOB ----------- ---------- ---------- ---------                  1 John               Brown        01-JAN-65                  2 Cynthia            Green        05-FEB-68

下面這個查詢檢索名字以J或j開頭的顧客。注意傳遞給REGEXP_LIKE()的Regex是 ^j，匹配選項是i，這說明不區分大小寫，因此在本例中，^j 可以匹配J或j：

代碼如下	複製代碼
SELECT customer_id, first_name, last_name, dob FROM customers WHERE REGEXP_LIKE(first_name, '^j', 'i');   CUSTOMER_ID FIRST_NAME LAST_NAME    DOB ----------- ---------- ---------- ---------                  1 John               Brown        01-JAN-65

2. REGEXP_INSTR()
REGEXP_INSTR(x, pattern [, start [, occurrence [, return_option [, match_option]]]])用於在x中尋找pattern；REGEXP_INSTR()返回pattern出現的位置。匹配位置從1開始。

下面這個查詢使用REGEXP_INSTR函數返回匹配Regex l[[:alpha:]]{4}的位置：

代碼如下	複製代碼
SELECT REGEXP_INSTR('But, soft! What light through yonder window breaks?', 'l[[:alpha:]]{4}') AS result FROM dual;   RESULT ---------- 17

注意傳回值為17，這是light中l的位置。

下面這個查詢返回第二次匹配Regex s[[:alpha:]]{3}的位置，匹配位置從1開始：

代碼如下	複製代碼
SELECT REGEXP_INSTR('But, soft! What light through yonder window softly breaks?', 's[[:alpha:]]{3}', 1, 2) AS result FROM dual;   RESULT ---------- 45

下面這個查詢使用REGEXP_INSTR函數返回第二次匹配字母 o 的位置，匹配位置從10開始：

代碼如下	複製代碼
SELECT REGEXP_INSTR('But, soft! What light through yonder window breaks?', 'o', 10, 2) AS result FROM dual;   RESULT ---------- 32

3. REGEXP_REPLACE()
REGEXP_REPLACE(x, pattern [, replace_string [, start [, occurrence[, match_option]]]])用於在x中尋找pattern，並將其替換為 replace_string。

下面這個查詢使用REGEXP_REPLACE函數將匹配Regex l[[:alpha:]]{4}的子字串替換為字串 sound：

代碼如下	複製代碼
SELECT REGEXP_REPLACE('But, soft! What light through yonder window breaks?', 'l[[:alpha:]]{4}', 'sound') AS result FROM dual;   RESULT

---------------------------------------------------

But, soft! What sound through yonder window breaks?

注意light已經被替換為sound。

4. REGEXP_SUBSTR()
REGEXP_SUBSTR(x, pattern[, start [, occurrence[, match_option]]])用於在x中尋找匹配pattern的子字串，開始位置由 start指定。

下面這個查詢使用REGEXP_SUBSTR函數返回匹配Regex l[[:alpha:]]{4}的子字串：

代碼如下	複製代碼
SELECT REGEXP_SUBSTR('But, soft! What light through yonder window breaks?', 'l[[:alpha:]]{4}') AS result FROM dual;   RESUL ----- light

5. REGEXP_COUNT()
REGEXP_COUNT()是Oracle Database11g新增加的一個函數。REGEXP_COUNT(x, pattern[, start [,match_option]])用於在x中尋找pattern，並返回pattern在x中出現的次數。可以提供選擇性參數start，指出要從x中開始尋找pattern的那個字元；也可以提供可選的match_option字串，指出匹配選項。

下面這個查詢使用REGEXP_COUNT函數返回Regexs[[:alpha:]]{3}出現的次數：

代碼如下	複製代碼
SELECT  REGEXP_COUNT('But, soft! What light through yonder window softly breaks?',   's[[:alpha:]]{3}') AS result FROM dual;       RESULT ----------  2

注意返回結果是2，這表明Regex在提供的字串中有兩次匹配。

•oracle正則表達匹配手機特號   
尾號四連號：([0123456789])111$   如：13498212222、13613431111

sql：select * from tb_phone where REGEXP_LIKE(phone_no,'([0123456789])111$')

尾號四連順：(0123|1234|2345|3456|4567|5678|6789)$  如：13576531234、13623432345

尾號倒四連順：(9876|8765|7654|6543|5432|4321|3210)$  如：13512329876、13676987654

尾號00XX：00[[:digit:]][[:digit:]]$  如：13512320023、13512320035

尾號AABB：([[:digit:]])1([[:digit:]])2$  如：13567545566

尾號ABAB：([[:digit:]]{2})1$   如：13545341212

尾號AAAB:([[:digit:]])11[[:digit:]]$  如：13564326667