Oracle正则表达式匹配

技术博客 (140) 2023-09-16 15:39:10

Oracle从10g开始支持正则表达式

元字符(metacharacters)

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 位置和顺序

Oracle正则表达式匹配 (https://mushiming.com/) 技术博客 第1张

ORACLE中的支持正则表达式的函数如下:

  • REGEXP_LIKE:用POSIX 正则表达式替代百分号(%)和通配符(_)字符进行模糊匹配;
  • REGEXP_INSTRinstr:返回一个字符串在另一字符串中的位置):返回正则表达式匹配的字符串在另一字符串中的位置;
  • REGEXP_SUBSTRinstr:根据指定位置截取字符串):使用正则来指定返回字符串的起点和终点;
  • REGEXP_REPLACE:替换字符串中的某个值,是REPLACE的增强版,支持正则表达式,扩展了一些功能;
  • REGEXP_COUNT:返回在源串中出现的模式的次数,作为对REGEXP_INSTR函数
    的补充。

1 . REGEXP_LIKE

regexp_like( source_string, pattern [match_parameter] )
  • source_string:源字符串
  • pattern :正则表达式
  • match_parameter:匹配模式
    i:不区分大小写;c:区分大小写;n:允许使用可以匹配任意字符串的操作符;m:将x作为一个包含多行的字符串。默认为’c’

例: 判断字符串是否以“201”或者“202”开头

SELECT 1 FROM DUAL WHERE REGEXP_LIKE('201','^201|202');  -- 结果为1
SELECT 1 FROM DUAL WHERE REGEXP_LIKE('200','^201|202');  -- 无结果
-- 也可以这么写
SELECT 1 FROM DUAL WHERE REGEXP_LIKE('202','^20[12]');  -- 结果为1

2. REGEXP_INSTR

3. REGEXP_SUBSTR

REGEXP_SUBSTR 函数使用正则表达式来指定返回串的起点和终点。

语法

regexp_substr(source_string,pattern[,position[,occurrence[,match_parameter]]])

source_string:源串,可以是常量,也可以是某个值类型为串的列。
position:从源串开始搜索的位置。默认为1。
occurrence:指定源串中的第几次出现。默认值1.
match_parameter:文本量,进一步订制搜索,取值如下:
'i'     用于不区分大小写的匹配。
'c'    用于区分大小写的匹配。
'n'    允许将句点“.”作为通配符来匹配换行符。如果省略改参数,句点将不匹配换行符。
'm'   将源串视为多行。即将“^”和“$”分别看做源串中任意位置任意行的开始和结束,
而不是看作整个源串的开始或结束。如果省略该参数,源串将被看作一行来处理。
如果取值不属于上述中的某个,将会报错。如果指定了多个互相矛盾的值,将使用最后一个值。如'ic'会被当做'c'处理。
省略该参数时:默认区分大小写、句点不匹配换行符、源串被看作一行。

例: 将表中PROVINCE字段中以市,省,自治区开头的部分截取出来。

SELECT  REGEXP_SUBSTR(PROVINCE,'.*?[市|省|自治区]+') FROM TABLE_NAME;
-- 结果为 
湖南省
上海市
宁夏自治区
四川省自
----------有问题 四川省这里把“自"也匹配进来了
----------修改如下,把字符串用括号包起来保证不被作为单个字符,而是字符串匹配
SELECT  REGEXP_SUBSTR(PROVINCE,'.*?([市]+|[省]+|[自治区]+)') FROM TABLE_NAME;

4. REGEXP_REPLACE

REPLACE 函数用于替换串中的某个值。
语法:
replace(char,search_string[,replace_string]) 如果不指定replace_string,会将搜索到的值删除。

REGEXP_REPLACEREPLACE 的增强版,支持正则表达式,扩展了一些功能。

语法

regexp_replace(source_string,pattern[,replace_string[,position[,occurrence[,match_parameter]]]])

例: 将表中NAME字段中间第二个字进行脱敏,改为 ”*“

SELECT REGEXP_REPLACE('王女士大幅回升','(.)','*',2,1) AS NAME
FROM DUAL ;
-- 表示从第2个字符开始,取任意1个字符,替换为*;;
-- 结果为 *士大幅回升

参考:

Oracle正则表达式使用介绍

Oracle中的正则表达式(及函数)详解

THE END

发表回复