ORACLE函数——支持正则表达式的函数

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

1、REGEXP_LIKE ：与LIKE的功能相似
2、REGEXP_INSTR ：与INSTR的功能相似
3、REGEXP_SUBSTR ：与SUBSTR的功能相似

4、REGEXP_REPLACE ：与REPLACE的功能相似

5、regexp_count

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

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

运算符	描述
^	匹配输入字符串的开始位置，在方括号表达式中使用，此时它表示不接受该字符集合。
$	匹配输入字符串的结尾位置。如果设置了 RegExp 对象的 Multiline 属性，则 $ 也匹配 '\n' 或 '\r'。
.	匹配除换行符之外的任何单字符
?	匹配前面的子表达式零次或一次
+	匹配前面的子表达式一次或多次
*	匹配前面的子表达式零次或多次
\|	指明两项之间的一个选择。例子'^([a-z]+\|[0-9]+)$'表示所有小写字母或数字组合成的字符串。
()	分组表达式，标记一个子表达式的开始和结束位置
[]	方括号表达式指定一个匹配列表，能包含子表达式。非匹配列表表达式以^开始，指定一个列表，除了列表中显示的表达式，该列表可以匹配任何字符。
{m,n}	一个精确地出现次数范围，m=<出现次数<=n； '{m}'表示出现m次； '{m,}'表示至少出现m次。
\n	n是介于1和9之间的数，反向引用表达式匹配圆括号和前面\n之间的第n个子表达式
[::]	[:alpha:] 任何字母。 [:digit:] 任何数字。 [:alnum:] 任何字母和数字。 [:space:] 任何白字符。 [:upper:] 任何大写字母。 [:lower:] 任何小写字母。 [:punct:] 任何标点符号。 [:xdigit:] 任何16进制的数字，相当于[0-9a-fA-F]。 [:graph:] 任何[:punct:]、[:lower:]、[:upper:]和[:digit:]字符
[==]	指定等价类。基本字母和它的重音构成等价类（如a和ā）

各种操作符的运算优先级
\转义符
(), (?:), (?=), [] 圆括号和方括号
*, +, ?, {n}, {n,}, {n,m} 限定符
^, $, anymetacharacter 位置和顺序
|

一、REGEXP_LIKE(source_string,pattern[,match_parameter])函数:

返回满足匹配模式的字符串。相当于增强的like函数。source_string指定源字符表达式；pattern指定规则表达式，match_parameter指定默认匹配操作的文本串，默认为（c）。

match_parameter
值	描述
‘c’	区分大小写的匹配
'i'	不区分大小写的匹配
'n'	允许句点字符(.)与换行符匹配。默认情况下，句点是通配符。
'm'	将源字符串视为多行。即Oracle 将^和$分别看作源字符串中任意位置任何行的开始和结束，而不是仅仅看作整个源字符串的开始或结束。如果省略该参数，则Oracle将源字符串看作一行。
'x'	忽略空格字符。默认情况下，空格字符与自身相匹配。

--查询任何包含标点符号的记录。[[:punct:]] 任何标点符号。

select 1 from dual where regexp_like('12','[[:punct:]]');不会返回任何值

二、

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

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

以下例子将字符串中返回’with’或‘or’的第一次出现,它将匹配一个词组。“1”为开始位置 “1”是搜索第一个匹配的，”0”是return_option 返回出现的第一个字符位置“i”是不区分大小写。
SELECT REGEXP_INSTR ('World filled with love', 'with|or', 1, 1, 0, 'i')FROM dual;
结果：2

三、REGEXP_SUBSTR(str1,pattem [,pos[,occ[,par]]])函数：

返回匹配模式的子字符串，扩展了SUBSTR函数的功能，并且用于按照特定表达式的规则返回字符串的子串。其中参数str1指定源字符表达式，pattem指定规则表达式， pos指定起始搜索位置（默认为1），occ指定替换出现的第几个字符串，par指定默认匹配操作的文本串。

Select REGEXP_SUBSTR('http://www.oracle.com/products','http://([[:alnum:]]+\.+[[:alnum:]]+\.+[[:alnum:]]+\.?)') from dual;

结果：http://www.oracle.com

结果：4

四、REGEXP_REPLACE(str1,pattem[,replace_str[,position[,occurtence[,par]]]])函数：

10g新增函数，扩展了REPLACE函数的功能，并且用于按照特定正则表达式的规则替换字符串。其中参数str1指定源字符表达式，pattem指定正则表达式，replace_str指定替换字符串，position从源串开始搜索的位置（默认为1），occurtence指定替换出现的第几个字符串，par指定默认匹配操作的文本串。

结果：*123.456

结果：3.4

其中"\1"、“\2”、“\3”分别表示第1、2、3个数据集。其他的，大家慢慢看就能看懂了。

扩展：

select regexp_replace('电话：023 5868-8888 邮箱：',
'.*([[:digit:]]{3})([^[:digit:]]{0,2})([[:digit:]]{4})([^[:digit:]]{0,2})([[:digit:]]{4}).*','(\1)\3\5'

) phone from dual;

结果：(023)

五、regexp_count(source_char,pattern[,position[,match_param]])函数：

返回pattern在source_char串中出现的次数。如果未找到匹配，函数返回0。

REGEXP_COUNT函数返回在源串中出现的模式的次数，作为对REGEXP_INSTR函数的补充。
虽然COUNT是一个集合函数，操作的是行组，但是REGEXP_COUNT是单行函数，分别计算每一行。

下列计算某字串在源串中出现的次数：
select (length('MY INFO: Anxpp,23，and boy')-length(replace('MY INFO: Anxpp,23，and boy','an')))/length('an') from dual;
结果：1
select regexp_count('MY INFO: Anxpp,23，and boy','an') from dual;
结果：1

今天的文章 ORACLE函数——支持正则表达式的函数分享到此就结束了，感谢您的阅读。