oracle正则表达式函数匹配

本是古典何须时尚 2022-04-21 14:05 295阅读 0赞

文章介绍了关于oracle正则函数的一些用法，包括匹配等,Oracle10g提供了在查询中使用正则表达的功能，它是通过各种支持正则表达式的函数在where子句中实现的。

ORACLE中的支持正则表达式的函数主要有下面四个：

1，REGEXP\_LIKE ：与LIKE的功能相似

2，REGEXP\_INSTR ：与INSTR的功能相似

3，REGEXP\_SUBSTR ：与SUBSTR的功能相似

4，REGEXP\_REPLACE ：与REPLACE的功能相似

1、正则表达式中的元字符  
元字符 意思 例子  
说明要匹配的字符是一个特殊字符、常量或者后者引用。（后引用重复上一次的匹配） 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是一个子正则表达式，匹配指定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:\]可以匹配非空字符  
\[:lower:\]可以匹配小写字母a-z  
\[:print:\]与\[:graph:\]类似，不同之处在于\[:print:\]包括空格字符  
\[:punct:\]可以匹配标点符号.,""等等  
\[:space:\]可以匹配所有的空字符  
\[:upper:\]可以匹配大写字母A-Z  
\[:xdigit:\]可以匹配十六进制数字0-9、A-F、a-f  
n 这是对前一次匹配命中的一个后引用，其中n是一个正整数 (.)1可以匹配两个连续相同的非空字符。(.)可以匹配除null以外的任何单个字符，而1则重复上一次匹配的内容，即再次匹配相同的字符，因此可以匹配两个连续相同的非空字符

2、REGEXP\_LIKE(x,pattern\[,match\_option\])用于在x中查找正则表达式pattern，该函数还可以提供一个可选的参数match\_option字符串说明默认的匹配选项。match\_option的取值如下：  
‘c’ 说明在进行匹配时区分大小写（缺省值）；  
'i' 说明在进行匹配时不区分大小写；  
'n' 允许使用可以匹配任意字符的操作符；  
'm' 将x作为一个包含多行的字符串。

\--测试数据

<table> 
 <tbody> 
  <tr> 
   <td>代码如下</td> 
   <td align="center">复制代码</td> 
  </tr> 
  <tr> 
   <td colspan="2"> <p>create table test(mc varchar2(60));</p> <p>insert into test values('112233445566778899'); <br> insert into test values('22113344 5566778899'); <br> insert into test values('33112244 5566778899'); <br> insert into test values('44112233 5566 778899'); <br> insert into test values('5511 2233 4466778899'); <br> insert into test values('661122334455778899'); <br> insert into test values('771122334455668899'); <br> insert into test values('881122334455667799'); <br> insert into test values('991122334455667788'); <br> insert into test values('aabbccddee'); <br> insert into test values('bbaaaccddee'); <br> insert into test values('ccabbddee'); <br> insert into test values('ddaabbccee'); <br> insert into test values('eeaabbccdd'); <br> insert into test values('ab123'); <br> insert into test values('123xy'); <br> insert into test values('007ab'); <br> insert into test values('abcxy'); <br> insert into test values('The final test is is is how to find duplicate words.');</p> <p>commit;</p> </td> 
  </tr> 
 </tbody> 
</table>

一、REGEXP\_LIKE

<table> 
 <tbody> 
  <tr> 
   <td>代码如下</td> 
   <td align="center">复制代码</td> 
  </tr> 
  <tr> 
   <td colspan="2"> <p>select * from test where regexp_like(mc,'^a{1,3}'); <br> select * from test where regexp_like(mc,'a{1,3}'); <br> select * from test where regexp_like(mc,'^a.*e$'); <br> select * from test where regexp_like(mc,'^[[:lower:]]|[[:digit:]]'); <br> select * from test where regexp_like(mc,'^[[:lower:]]'); <br> Select mc FROM test Where REGEXP_LIKE(mc,'[^[:digit:]]'); <br> Select mc FROM test Where REGEXP_LIKE(mc,'^[^[:digit:]]');</p> </td> 
  </tr> 
 </tbody> 
</table>

二、REGEXP\_INSTR

<table> 
 <tbody> 
  <tr> 
   <td>代码如下</td> 
   <td align="center">复制代码</td> 
  </tr> 
  <tr> 
   <td colspan="2"> <p>Select REGEXP_INSTR(mc,'[[:digit:]]$') from test; <br> Select REGEXP_INSTR(mc,'[[:digit:]]+$') from test; <br> Select REGEXP_INSTR('The price is $400.','$[[:digit:]]+') FROM DUAL; <br> Select REGEXP_INSTR('onetwothree','[^[[:lower:]]]') FROM DUAL; <br> Select REGEXP_INSTR(',,,,,','[^,]*') FROM DUAL; <br> Select REGEXP_INSTR(',,,,,','[^,]') FROM DUAL;</p> </td> 
  </tr> 
 </tbody> 
</table>

三、REGEXP\_SUBSTR

<table> 
 <tbody> 
  <tr> 
   <td>代码如下</td> 
   <td align="center">复制代码</td> 
  </tr> 
  <tr> 
   <td colspan="2"> <p>SELECT REGEXP_SUBSTR(mc,'[a-z]+') FROM test; <br> SELECT REGEXP_SUBSTR(mc,'[0-9]+') FROM test; <br> SELECT REGEXP_SUBSTR('aababcde','^a.*b') FROM DUAL;</p> </td> 
  </tr> 
 </tbody> 
</table>

四、REGEXP\_REPLACE

<table> 
 <tbody> 
  <tr> 
   <td>代码如下</td> 
   <td align="center">复制代码</td> 
  </tr> 
  <tr> 
   <td colspan="2"> <p>Select REGEXP_REPLACE('Joe Smith','( ){2,}', ',') AS RX_REPLACE FROM dual; <br> Select REGEXP_REPLACE('aa bb cc','(.*) (.*) (.*)', '3, 2, 1') FROM dual；</p> <p>SQL&gt; select * from test;</p> <p>ID MC <br> -------------------- ------------------------------------------------------------ <br> A AAAAA <br> a aaaaa</p> <p>b bbbbb</p> </td> 
  </tr> 
 </tbody> 
</table>

SQL> select \* from test where regexp\_like(id,'b','i'); --不区分数据大小写

ID MC  
\-------------------- ------------------------------------------------------------

b bbbbb

\#End

接下来的几节将会介绍更多有关正则表达式函数的知识。

1. REGEXP\_LIKE()  
REGEXP\_LIKE(x, pattern \[, match\_option\])用于在x中查找pattern参数中定义的正则表达式，该函数还可以提供一个可选参数match\_option，它可以设置为下面几个字符之一：

'c'，说明在匹配时区分大小写(默认选项)  
'I'，说明在匹配时不区分大小写  
'n'，允许使用可以匹配任意字符的操作符  
'm'，将x 作为一个包含多行的字符串  
下面这个查询使用REGEXP\_LIKE函数检索生日在1965年到1968年之间的顾客：

<table> 
 <tbody> 
  <tr> 
   <td>代码如下</td> 
   <td align="center">复制代码</td> 
  </tr> 
  <tr> 
   <td colspan="2"> <p>SELECT customer_id, first_name, last_name, dob</p> <p>FROM customers</p> <p>WHERE REGEXP_LIKE(TO_CHAR(dob, 'YYYY'), '^196[5-8]$');</p> <p></p> <p>CUSTOMER_ID FIRST_NAME LAST_NAME DOB</p> <p>----------- ---------- ---------- ---------</p> <p> 1 John Brown 01-JAN-65</p> <p> 2 Cynthia Green 05-FEB-68</p> </td> 
  </tr> 
 </tbody> 
</table>

下面这个查询检索名字以J或j开头的顾客。注意传递给REGEXP\_LIKE()的正则表达式是 ^j，匹配选项是i，这说明不区分大小写，因此在本例中，^j 可以匹配J或j：

<table> 
 <tbody> 
  <tr> 
   <td>代码如下</td> 
   <td align="center">复制代码</td> 
  </tr> 
  <tr> 
   <td colspan="2"> <p>SELECT customer_id, first_name, last_name, dob</p> <p>FROM customers</p> <p>WHERE REGEXP_LIKE(first_name, '^j', 'i');</p> <p></p> <p>CUSTOMER_ID FIRST_NAME LAST_NAME DOB</p> <p>----------- ---------- ---------- ---------</p> <p> 1 John Brown 01-JAN-65</p> </td> 
  </tr> 
 </tbody> 
</table>

2. REGEXP\_INSTR()  
REGEXP\_INSTR(x, pattern \[, start \[, occurrence \[, return\_option \[, match\_option\]\]\]\])用于在x中查找pattern；REGEXP\_INSTR()返回pattern出现的位置。匹配位置从1开始。

下面这个查询使用REGEXP\_INSTR函数返回匹配正则表达式 l\[\[:alpha:\]\]\{4\}的位置：

<table> 
 <tbody> 
  <tr> 
   <td>代码如下</td> 
   <td align="center">复制代码</td> 
  </tr> 
  <tr> 
   <td colspan="2"> <p>SELECT</p> <p>REGEXP_INSTR('But, soft! What light through yonder window breaks?',</p> <p>'l[[:alpha:]]{4}') AS result</p> <p>FROM dual;</p> <p></p> <p>RESULT</p> <p>----------</p> <p>17</p> </td> 
  </tr> 
 </tbody> 
</table>

注意返回值为17，这是light中l的位置。

下面这个查询返回第二次匹配正则表达式 s\[\[:alpha:\]\]\{3\}的位置，匹配位置从1开始：

<table> 
 <tbody> 
  <tr> 
   <td>代码如下</td> 
   <td align="center">复制代码</td> 
  </tr> 
  <tr> 
   <td colspan="2"> <p>SELECT</p> <p>REGEXP_INSTR('But, soft! What light through yonder window softly breaks?',</p> <p>'s[[:alpha:]]{3}', 1, 2) AS result</p> <p>FROM dual;</p> <p></p> <p>RESULT</p> <p>----------</p> <p>45</p> </td> 
  </tr> 
 </tbody> 
</table>

下面这个查询使用REGEXP\_INSTR函数返回第二次匹配字母 o 的位置，匹配位置从10开始：

<table> 
 <tbody> 
  <tr> 
   <td>代码如下</td> 
   <td align="center">复制代码</td> 
  </tr> 
  <tr> 
   <td colspan="2"> <p>SELECT</p> <p>REGEXP_INSTR('But, soft! What light through yonder window breaks?',</p> <p>'o', 10, 2) AS result</p> <p>FROM dual;</p> <p></p> <p>RESULT</p> <p>----------</p> <p>32</p> </td> 
  </tr> 
 </tbody> 
</table>

3. REGEXP\_REPLACE()  
REGEXP\_REPLACE(x, pattern \[, replace\_string \[, start \[, occurrence\[, match\_option\]\]\]\])用于在x中查找pattern，并将其替换为 replace\_string。

下面这个查询使用REGEXP\_REPLACE函数将匹配正则表达式 l\[\[:alpha:\]\]\{4\}的子字符串替换为字符串 sound：

<table> 
 <tbody> 
  <tr> 
   <td>代码如下</td> 
   <td align="center">复制代码</td> 
  </tr> 
  <tr> 
   <td colspan="2"> <p>SELECT</p> <p>REGEXP_REPLACE('But, soft! What light through yonder window breaks?',</p> <p>'l[[:alpha:]]{4}', 'sound') AS result</p> <p>FROM dual;</p> <p></p> <p>RESULT</p> </td> 
  </tr> 
 </tbody> 
</table>

\---------------------------------------------------

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函数返回匹配正则表达式 l\[\[:alpha:\]\]\{4\}的子字符串：

<table> 
 <tbody> 
  <tr> 
   <td>代码如下</td> 
   <td align="center">复制代码</td> 
  </tr> 
  <tr> 
   <td colspan="2"> <p>SELECT</p> <p>REGEXP_SUBSTR('But, soft! What light through yonder window breaks?',</p> <p>'l[[:alpha:]]{4}') AS result</p> <p>FROM dual;</p> <p></p> <p>RESUL</p> <p>-----</p> <p>light</p> </td> 
  </tr> 
 </tbody> 
</table>

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函数返回正则表达式s\[\[:alpha:\]\]\{3\}出现的次数：

<table> 
 <tbody> 
  <tr> 
   <td>代码如下</td> 
   <td align="center">复制代码</td> 
  </tr> 
  <tr> 
   <td colspan="2"> <p>SELECT</p> <p>REGEXP_COUNT('But, soft! What light through yonder window softly breaks?',</p> <p> 's[[:alpha:]]{3}') AS result</p> <p>FROM dual;</p> <p></p> <p> RESULT</p> <p>----------</p> <p>2</p> </td> 
  </tr> 
 </tbody> 
</table>

注意返回结果是2，这表明正则表达式在提供的字符串中有两次匹配。

•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

http://www.111cn.net/database/Oracle/43706.htm