Oracle正则表达式的用法

Oracle正则表达式的用法

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

一、Oracle预定义的 POSIX 字符类

字符类 说明
[:alpha:] 字母字符
[:lower:] 小写字母字符
[:upper:] 大写字母字符
[:digit:] 数字
[:alnum:] 字母数字字符
[:space:] 空白字符(禁止打印),如回车符、换行符、竖直制表符和换页符
[:punct:] 标点字符
[:cntrl:] 控制字符(禁止打印)
[:print:] 可打印字符

[:alnum:]

字母和数字混合的字符

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

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;

 

  • (3)、
  •  
  • SQL 代码   复制
  • 
    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_positionoccurrence 参数默认为 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)、

  •  
  • SQL 代码   复制
  • 
    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、NVARCHAR2NCLOB,但不包括 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)、

  • SQL 代码   复制
  • 
    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)、

  •  
  • SQL 代码   复制
  • 
    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;