Oracle正则表达式函数regexp_like、regexp_substr、regexp_instr、regexp_replace

正则表达式在Oracle10g中的应用--REGEXP_INSTR函数

 

regexp_like 只能用于条件表达式，和 like 类似，但是使用的正则表达式进行匹配  

 

regexp_substr 函数，和 substr 类似，用于拾取合符正则表达式描述的字符子串 

 

regexp_instr 函数，和 instr 类似，用于标定符合正则表达式的字符子串的开始位置  

 

regexp_replace 函数，和 replace 类似，用于替换符合正则表达式的字符串  

了解REGEXP_INSTR函数以前，我们先来了解INSTR函数。INSTR返回的是一个字符串在另外一个字符串中的位置，如字符串world在字符串hello world中的位置是7，即下面的语句返回值为7。

SELECT INSTR('hello world','world') FROM DUAL;

REGEXP_INSTR实现的功能与INSTR类似，不同的是REGEXP_INSTR用一个匹配模式代替了具体的字符串，这样就提供了更大的灵活性。下面的这个例子使用REGEXP_INSTR在字符串Joe Smith, 10045 Berry Lane, San Joeseph, CA 91234中返回5个数字组成的邮政编码的起始位置。如果正则表达式写为[[:digit:]]{5}，你将会取得房间号10045而不是邮政编码91234，因为10045也是5个连续的数字。因此，我们要将正则表达式锚在行尾，这要用到元字符$，这样这个函数就会返回邮政编码在字符串中的位置，而不会受到房间号的数字数量的影响了。

SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234','[[:digit:]]{5}$') AS rx_instr

FROM DUAL;

我们现在来扩展这个邮政编码，在前面的例子的基础上增加一个可选的4位数字。其模式为：[[:digit:]]{5}(-[[:digit:]]{4})?$。如果源字符串以5数字，或者以5数字+4数字邮政编码结束，你还是可以看到这个模式在串中的起始位置。

SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234','[[:digit:]]{5}(-[[:digit:]]{4})?$') AS rx_instr

FROM DUAL;

SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234-1234','[[:digit:]]{5}(-[[:digit:]]{4})?$')

AS rx_instr

FROM DUAL;

在这个例子中，在圆扩号内的子表达式(-[[:digit:]]{4})重复0次或1次，这是由重复操作符?来指定的。如果你试图用传统的SQL语句来实现相同的功能，那么即使是SQL专家也会感到困难的。

REGEXP_SUBSTR函数

了解REGEXP_SUBSTR函数以前，我们先来了解SUBSTR函数。SUBSTR函数利用参数指定的位置和字符个数返回一个字符串的子串，如SUBSTR(‘hello world’,1,5)返回子字符串hello。

SELECT SUBSTR('hello world',1,5) FROM DUAL;

REGEXP_SUBSTR函数是利用匹配模式从主字符串中抽取子串。在下面的例子中，函数返回匹配模式“, [^,]*,”的子字符串。正则表达式搜索一个逗号，后接一个空格；然后是0个或多个非逗号的字符（由[^,]*指定），最后是另一个逗号。这个模式类似于查找由逗号分隔的字符串。

SELECT REGEXP_SUBSTR('first field, second field, third field', ', [^,]*,') REGEXP_SUBSTR FROM DUAL;

 

查询结果为：

REGEXP_SUBSTR

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

, second field,

REGEXP_REPLACE函数

了解REGEXP_REPLACE函数以前，我们先来了解REPLACE函数。REPLACE函数根据指定的替代规则将字符串中的子串替换为另一个子串，如REPLACE(‘JACK and JUE’,’J’,’BL’)，其替换结果为’BLACK and BLUE’。

SELECT REPLACE('JACK and JUE','J','BL') FROM DUAL;

REPLACE函数的另外一个用途就是替换多余的空格，如在字符串Joe   Smith中，Joe和Smith之间有3个空格，我们将这三个空格替换为一个。

SELECT REPLACE('Joe   Smith','   ',' ') FROM DUAL;

利用REGEXP_REPLACE函数可以做进一步的替换，下面的查询将两个或者多个空格替换为一个空格，( )子表达式包含一个空格，这个空格可以重复两次或多次，这个重复次数是由{2,}指定的。

SELECT REGEXP_REPLACE('500   Oracle       Parkway,    Redwood   Shores, CA', '( ){2,}',' ') REGEXP_REPLACE

FROM DUAL;

 

查询结果为：

REGEXP_REPLACE

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

500 Oracle Parkway, Redwood Shores, CA

实验

以上测试在Oracle10g数据库中均可实现，由于所用到的都是DUAL表，因此通过任何用户连接到Oracle都可以。

 

它们在用法上与Oracle SQL 函数LIKE、INSTR、SUBSTR 和REPLACE 用法相同，  

但是它们使用POSIX 正则表达式代替了老的百分号（%）和通配符（_）字符。  

POSIX 正则表达式由标准的元字符（metacharacters）所构成：  

'^' 匹配输入字符串的开始位置，在方括号表达式中使用，此时它表示不接受该字符集合。  

'$' 匹配输入字符串的结尾位置。如果设置了 RegExp 对象的 Multiline 属性，则 $ 也匹  

配 '\n' 或 '\r'。  

'.' 匹配除换行符之外的任何单字符。  

'?' 匹配前面的子表达式零次或一次。  

'+' 匹配前面的子表达式一次或多次。  

'*' 匹配前面的子表达式零次或多次。  

'|' 指明两项之间的一个选择。例子'^([a-z]+|[0-9]+)$'表示所有小写字母或数字组合成的  

字符串。  

 

'( )' 标记一个子表达式的开始和结束位置。  

'[]' 标记一个中括号表达式。  

'{m,n}' 一个精确地出现次数范围，m=<出现次数<=n，'{m}'表示出现m次，'{m,}'表示至少  

出现m次。  

\num 匹配 num，其中 num 是一个正整数。对所获取的匹配的引用。  

字符簇：   

[[:alpha:]] 任何字母。  

[[:digit:]] 任何数字。  

[[:alnum:]] 任何字母和数字。  

[[:space:]] 任何白字符。  

[[:upper:]] 任何大写字母。  

[[:lower:]] 任何小写字母。  

[[:punct:]] 任何标点符号。  

[[:xdigit:]] 任何16进制的数字，相当于[0-9a-fA-F]。  

各种操作符的运算优先级  

\转义符  

(), (?:), (?=), [] 圆括号和方括号  

*, +, ?, {n}, {n,}, {n,m} 限定符  

^, $, anymetacharacter 位置和顺序  

|   

*/  

 

格式：

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

参数说明：

source_string：输入的字符串

pattern：正则表达式

position：标识从第几个字符开始正则表达式匹配。（默认为1）

occurrence：标识第几个匹配组。（默认为1）

return_option：0——返回第一个字符出现的位置。1：pattern下一个字符起始位置。

match_parameter：取值范围

        i：大小写不敏感；

        c：大小写敏感；

        n：点号 . 不匹配换行符号；

        m：多行模式；

        x：扩展模式，忽略正则表达式中的空白字符。 

REGEXP_SUBSTR函数中

 

SELECT REGEXP_SUBSTR('first field, second field, third field', ', [^,]*,') REGEXP_SUBSTR FROM DUAL;

 

与SELECT REGEXP_SUBSTR('first field, second field, third field', ', .*,') REGEXP_SUBSTR FROM DUAL;

效果相同 , second field,

 

select REGEXP_SUBSTR('111,aaaa,222','[^,]+',1,2) from dual

select REGEXP_SUBSTR('111,aaaa,222','a[a]*a') from dual

效果相同 aaaa

 

注意正则[^ ]意思是取反 如果想分割字符串取所需 利用[^,]分割 occurrence项取到所需的字符串。

 

select regexp_substr('xxlsm:3:10','[^:]+',1,2) from dual

取到3

也可以结合instr函数取到所需字符串，找到lsm: 后面的数字，可以任意截取

select substr('xxlsm:3xxx:10',instr('xxlsm:3xxx:10','lsm:')+4,1) from dual

取到其中的3