Oracle正则表达式的用法

Oracle正则表达式的用法

正则表达式具有强大、便捷、高效的文本处理功能。能够添加、删除、分析、叠加、插入和修整各种类型的文本和数据。Oracle从10g开始支持正则表达式

一、Oracle预定义的 POSIX 字符类

二、正则表达式运算符和函数

1、REGEXP_SUBSTR

 REGEXP_SUBSTR为指定字符串的一部分与正则表达式建立匹配。

语法如下：

REGEXP_SUBSTR(source_string,

pattern,

start_position,

occurrence,

match_parameter)

说明

其中source_string是必须的。可以是带引号的字符串或者变量。

Pattern是用单引号引用的与正则表达式。

Start_position指定了在字符串中的准确位置，默认值为1。

Occurrence是一个选项，指定在源字符串匹配过程中相对其他字符串，哪个字符串应该匹配。

最后，match_parameter也是一个选项，指定在匹配时是否区分大水写。

实例

（1）、返回从ename的第二个位置开始查找，并且是以“L”开头到结尾的字串

SQL> select regexp_substr(ename,'L.*','2') substr from emp;
 

（2）、

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

DECLARE    
    V_RESULT VARCHAR2(255);    
BEGIN    
    --返回‘light’   
    SELECT REGEXP_SUBSTR('But, soft! What light through yonder window breaks？','l[[:alpha:]]{4}') INTO V_RESULT    
    FROM DUAL;          
    DBMS_OUTPUT.PUT_LINE(V_RESULT);         
END;    

		

2、REGEXP_INSTR

REGEXP_INSTR返回与正则表达式匹配的字符和字符串的位置。

语法

REGEXP_INSTR(source_string, pattern
[, start_position
[, occurrence
[, return_option
[, match_parameter]]]])

参数：

'start' 开始查找的位置；
'occurrence' 说明应该返回第几次出现pattern的位置；
'eturn_option' 说明应该返回什么整数。若该参数为0，则说明要返回的整数是x中的一个字符的位置；若该参数为非0的整数，则说明要返回的整数为x中出现在pattern之后的字符的位置；
'match_option' 修改默认的匹配设置。
 

说明

该函数查找 pattern ，并返回该模式的第一个位置。您可以随意指定您想要开始搜索的 start_position。 occurrence 参数默认为 1，除非您指定您要查找接下来出现的一个模式。return_option 的默认值为 0，它返回该模式的起始位置；值为 1 则返回符合匹配条件的下一个字符的起始位置。

实例

（1）、

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;
 

（2）、查找员工编号中第一个非数字字符的位置

select regexp_instr(empno,'[^[:digit:]]') position from emp;

（3）、从第三个字符开始，查找员工编号中第二个非数字字符的位置

select regexp_instr(empno,'[^[:digit:]]',3,2) position from emp;

（4）、

DECLARE  
    V_RESULT INTEGER;  
BEGIN  
    --返回17，找出l开头的 后跟4个任意字母的单词在第一个参数中第一次出现的位置，这里是light中l的位置   
    SELECT REGEXP_INSTR('But, soft! What light through yonder window breaks？','l[[:alpha:]]{4}',1,1,0) INTO V_RESULT  
    FROM DUAL;  
      
    DBMS_OUTPUT.PUT_LINE(V_RESULT);  
      
    --返回22，找出l开头的 后跟4个任意字母的单词在第一个参数中第一次出现的位置，这里是light中t的位置+1   
    SELECT REGEXP_INSTR('But, soft! What light through yonder window breaks？','l[[:alpha:]]{4}',1,1,1) INTO V_RESULT  
    FROM DUAL;  
      
    DBMS_OUTPUT.PUT_LINE(V_RESULT);  
END;  

		

3、REGEXP_LIKE

REGEXP_LIKE运算符与LIKE运算符相似，但是功能更强大，因为它支持使用与此正则表达式与文本进行匹配。

语法

REGEXP_LIKE(source_string, pattern, match_parameter)

参数说明

source_string 支持字符数据类型（CHAR、VARCHAR2、CLOB、NCHAR、NVARCHAR2 和 NCLOB，但不包括 LONG）。pattern 参数是正则表达式的另一个名称。match_parameter 允许可选的参数（如处理换行符、保留多行格式化以及提供对区分大小写的控制）。

match_option的取值如下：

‘c’ 说明在进行匹配时区分大小写（缺省值）；
'i' 说明在进行匹配时不区分大小写；
'n' 允许使用可以匹配任意字符的操作符；
'm' 将x作为一个包含多行的字符串。
 

实例

（1）、

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:]]');
 

（2）、查找员工编号为4位数字的员工信息

select empno,ename from emp where regexp_like(empno,'^[[:digit:]]{4}$');
或者:

select empno,ename from emp where regexp_like(empno,'^[0-9]{4}$');

（3）、查找员工姓名为全英文的员工信息

select empno,ename from emp where regexp_like(ename,'^[[:alpha:]]+$');
或者:

select * from emp where regexp_like(ename,'^[a-zA-Z]+$');

（4）、查找员工姓名以“a”字母开头，不区分大小写

select empno,ename from emp where regexp_like(ename,'^a','i');
 

（5）、查找员工姓名为全英文，且以“N”结尾的员工信息

select empno,ename from emp where regexp_like(ename,'^[[:alpha:]]+N$');
 

（6）、查找员工编号以非数字开头的员工信息

select empno,ename from emp where regexp_like(empno,'[^[:digit:]]');

（7）、

DECLARE  
    V_FIRST_NAME VARCHAR2(50);  
    V_DOB DATE;  
BEGIN  
    --返回1965－1968你出生的FIRST_NAME以‘J’开头的消费者   
    SELECT FIRST_NAME,DOB INTO V_FIRST_NAME,V_DOB  
    FROM CUSTOMERS  
    WHERE REGEXP_LIKE(TO_CHAR(DOB,'YYYY'),'^196[5-8]$') AND REGEXP_LIKE(FIRST_NAME,'^J');  
      
    DBMS_OUTPUT.PUT_LINE(V_FIRST_NAME);  
      
END;  

		

4、REGEXP_REPLACE

语法

REGEXP_REPLACE(x,pattern[,replace_string[,start[,occurrence[, match_option]]]])

说明

REGEXP_REPLACE与REPLACE函数类似，提供一种修改与所给正则表达式匹配的字符串的方法。该函数用一个指定的 replace_string 来替换匹配的模式，从而允许复杂的“搜索并替换”操作。

实例

（1）、

Select REGEXP_REPLACE('Joe Smith','( ){2,}', ',') AS RX_REPLACE FROM dual;
Select REGEXP_REPLACE('aa bb cc','(.*) (.*) (.*)', '3, 2, 1') FROM dual；
 

（2）、把ename中所有非字母字符替换为“A”

update emp set ename=regexp_replace(ename, '[^[:alpha:]]', 'A')  where regexp_like(ename, '[^[:alpha:]]');

（3）、

DECLARE    
    V_RESULT VARCHAR2(255);    
BEGIN    
    --返回But, soft! What XXX through yonder window breaks？ 用‘XXX’代替‘light’   
    SELECT REGEXP_REPLACE('But, soft! What light through yonder window breaks？','l[[:alpha:]]{4}','XXX') INTO V_RESULT    
    FROM DUAL;          
    DBMS_OUTPUT.PUT_LINE(V_RESULT);         
END;