备注:测试数据库版本为Oracle 11g R2
这个blog我们来聊聊Oracle的正则表达式
一.正则表达式集合及操作符 1.字符集合:

字符	匹配的字符
\d	从０-９的任一数字
\D	任一非数字字符
\w	任一单词字符，包括A-Z,a-z,0-9和下划线
\W	任一非单词字符
\s	任一空白字符，包括制表符，换行符，回车符，换页符和垂直制表符
\S	任一非空白字符
.	任一字符

字符簇:

[[:alpha:]] 任何字母。 [[:digit:]] 任何数字。 [[:alnum:]] 任何字母和数字。 [[:space:]] 任何空白字符。 [[:upper:]] 任何大写字母。 [[:lower:]] 任何小写字母。 [[:punct:]] 任何标点符号。 [[:xdigit:]] 任何16进制的数字，相当于[0-9a-fA-F]。

2.边界集合:

字符 l	描述
^	每一行的开头，单行模式下等价于字符串的开头
$	每一行的结尾，单行模式下等价于字符串的结尾

3.重复次数集合: 贪婪模式会获取尽可能多的字符，而非贪婪模式会获取尽可能少的字符。

贪婪	非贪婪	描述
*	*?	零次或多次
?	??	零次或一次
+	+?	一次或多次
{m}	{m}?	正好m次，贪婪与非贪婪一样的
{m,}	{m,}?	至少m次
{m, n}	{m, n}?	最少m最多n次

4.组合操作符: 优先级比较：圆括号>重复次数操作符>和>或

组合操作符	描述
[…]	方括号内任意字符或字符集合中的一个
[^…]	方括号内^为第一个字符时，表示与其后所有字符都不匹配的字符
(…)	圆括号，将复杂表达式当作单一表达式来处理
..|..	或
abc	和。直接将字符连在一起写

5.匹配操作符:

匹配操作符	描述
\n	即后向引用。n为1~9，标识由圆括号里取得的匹配字符串。方向是从左到右在regexp_replace函数中，允许在模式表达式和替换表达式中都使用\n

6.转义操作符:

转义操作符	描述
\	将其后紧跟着的操作字符当作普通字符看待。例如 abcdef 可以匹配 abdef或abcccdef等，但无法匹配 abcdef，后者需要abc*def才能匹配

二.正则表达式语法

regexp_count(src, pattern[, pos[, match]]) regexp_instr(src, pattern[, pos[, occur[, retopt[, match[, subexpr]]]]]) regexp_like(src, pattern[, match]) regexp_substr(src, pattern[, pos[, occur[, match[, subexpr]]]]) regexp_replace(src, pattern[, replace_str[, pos[, occur[, match]]]])

参数释义:

– src：需要用来进行分析的字符串，如"aaaa","welcome","abc12cdg"等; – pattern：要匹配的正则表达式; – pos：从源字符串的第几个字符开始匹配; – occur：指定pattern的第几次出现; – retopt：返回选项，regexp_instr专用。用于设置返回值是pattern出现之时的位置还是之后 的位置。 0代表前者，1代表后者; – replace_str：要替换成的字符串，可以使用正则表达式中的后向引用操作符; – subexpr：regexp_instr和regexp_substr特有。指定返回pattern的 第几个子表达式。 0表示返回整个pattern匹配的字符串。例如0123(((abc)(de)f)ghi)45(678) 就包含abcdefghi、 abcdef、 abc、 de、 678这五个子串（已按顺序排列）; – match：匹配参数配置，让正则更易用。 ‘i’ 用于不区分大小写的匹配 ‘c’ 用于区分大小写的匹配 ‘n’ 允许句点(.)作为通配符去匹配换行符。如果省略该参数，则句点将不匹配换行符 ‘m’ 将源串视为多行。即Oracle 将^和$分别看作源串中任意位置任何行的开始和结束，而不是仅仅看作整个源串 的开始或结束。如果省略该参数，则Oracle将源串看作一行。 ‘x’ 忽略空格字符。默认情况下，空格字符与自身相匹配。

三.正则表达式功能 1.regexp_count regexp_count语法:

regexp_count(src, pattern[, pos[, match]])

regexp_count用于求原字符串中某个匹配字符出现的次数

--匹配字符串中某个字符出现的次数 SELECT REGEXP_COUNT('aaa,bbb,ccc,ddd,eee',',') AS cot FROM DUAL; --遇到通配符要使用\转义 SELECT REGEXP_COUNT('aaa$,bbb$,ccc$,ddd$,eee', '\$,') AS cot FROM DUAL; --从第10个字符串开始匹配 SELECT REGEXP_COUNT('aaa,bbb,ccc,ddd,eee',',',10) AS cot FROM DUAL; --默认是区分大小写的匹配 SELECT REGEXP_COUNT('abc,ABC,AAA,aaa','a') AS cot FROM DUAL; --设置成不区分大小写的匹配 SELECT REGEXP_COUNT('abc,ABC,AAA,aaa','a',1,'i') AS cot FROM DUAL;

SQL> --匹配字符串中某个字符出现的次数 SQL> SELECT REGEXP_COUNT('aaa,bbb,ccc,ddd,eee',',') AS cot FROM DUAL; COT ---------- 4 SQL> --遇到通配符要使用\转义 SQL> SELECT REGEXP_COUNT('aaa$,bbb$,ccc$,ddd$,eee', '\$,') AS cot FROM DUAL; COT ---------- 4 SQL> --从第10个字符串开始匹配 SQL> SELECT REGEXP_COUNT('aaa,bbb,ccc,ddd,eee',',',10) AS cot FROM DUAL; COT ---------- 2 SQL> --默认是区分大小写的匹配 SQL> SELECT REGEXP_COUNT('abc,ABC,AAA,aaa','a') AS cot FROM DUAL; COT ---------- 4 SQL> --设置成不区分大小写的匹配 SQL> SELECT REGEXP_COUNT('abc,ABC,AAA,aaa','a',1,'i') AS cot FROM DUAL; COT ---------- 8

2.regexp_replace regexp_replace语法:

regexp_replace(src, pattern[, replace_str[, pos[, occur[, match]]]])

regexp_replace是replace的加强版，使用频率非常高

--去掉字符串中的AEIOU字符 select regexp_replace('ABCDEFGHI','[AEIOU]') from dual; --去掉字符串中的非AEIOU字符 select regexp_replace('ABCDEFGHI','[^AEIOU]') from dual; --从第5个字符开始去掉字符串中的AEIOU字符 select regexp_replace('ABCDEFGHI','[AEIOU]','',5) from dual; --从第5个字符开始去掉第二次出现字符串中的AEIOU字符 select regexp_replace('ABCDEFGHI','[AEIOU]','',5,2) from dual; --拓展1 取名字中的大写字母 SELECT regexp_replace(data, '([[:upper:]])(.*)([[:upper:]])(.*)', '\1.\3') FROM (SELECT 'Bill Gates' data FROM dual UNION ALL SELECT 'Michael Jordan' data FROM dual UNION ALL SELECT 'Kobe Bryant' data FROM dual);

SQL> --去掉字符串中的AEIOU字符 SQL> select regexp_replace('ABCDEFGHI','[AEIOU]') from dual; REGEXP_REPLACE('ABCDEFGHI','[A ------------------------------ BCDFGH SQL> --去掉字符串中的非AEIOU字符 SQL> select regexp_replace('ABCDEFGHI','[^AEIOU]') from dual; REGEXP_REPLACE('ABCDEFGHI','[^ ------------------------------ AEI SQL> --从第5个字符开始去掉字符串中的AEIOU字符 SQL> select regexp_replace('ABCDEFGHI','[AEIOU]','',5) from dual; REGEXP_REPLACE('ABCDEFGHI','[A ------------------------------ ABCDFGH SQL> --从第5个字符开始去掉第二次出现字符串中的AEIOU字符 SQL> select regexp_replace('ABCDEFGHI','[AEIOU]','',5,2) from dual; REGEXP_REPLACE('ABCDEFGHI','[A ------------------------------ ABCDEFGH SQL> --拓展1 取名字中的大写字母 SQL> SELECT regexp_replace(data, '([[:upper:]])(.*)([[:upper:]])(.*)', '\1.\3') 2FROM (SELECT 'Bill Gates' data 3FROM dual 4UNION ALL 5SELECT 'Michael Jordan' data 6FROM dual 7UNION ALL 8SELECT 'Kobe Bryant' data 9FROM dual); REGEXP_REPLACE(DATA,'([[:UPPER -------------------------------------------------------------------------------- B.G M.J K.B

3.regexp_like regexp_like语法:

regexp_like(src, pattern[, match])

regexp_like是like的加强版，使用频率非常高

--regexp_like(src,'^A') 相当于:like 'A%' SELECT str from ( select 'A' str from dual union all select 'AB' str from dual union all select 'BA' str from dual union all select 'BACD' str from dual ) whereregexp_like(str,'^A'); --regexp_like(src,'A$') 相当于:like '%A' SELECT str from ( select 'A' str from dual union all select 'AB' str from dual union all select 'BA' str from dual union all select 'BACD' str from dual ) whereregexp_like(str,'A$'); --regexp_like(src,'^A$')相当于:like 'A' SELECT str from ( select 'A' str from dual union all select 'AB' str from dual union all select 'BA' str from dual union all select 'BACD' str from dual ) whereregexp_like(str,'^A$'); --regexp_like(str,'^[ABC]')相当于： like '%A%' or like'%B%'or like'%C%' SELECT str FROM (select 'A' str from dual union all select 'AB' str from dual union all select 'BA' str from dual union all select 'BACD' str from dual) WHERE regexp_like(str,'^[ABC]'); --regexp_like(src,'[0-9a-zA-Z]+') 相当于：like '%数字%' or like '%小写字母%' or like '%大写字母%' SELECT str FROM (SELECT '12345' str FROM dual UNION ALL SELECT 'abcde' str FROM dual UNION ALL SELECT '12cde' str FROM dual UNION ALL SELECT 'abc12' str FROM dual UNION ALL SELECT 'a1b2cde' str FROM dual UNION ALL SELECT '12345#' str FROM dual UNION ALL SELECT 'abcde#' str FROM dual) WHERE regexp_like(str, '^[0-9a-zA-Z]+$'); --"+"表示匹配前面的子表达式一次或多次 SELECT str from ( select '167' str from dual union all select '1234567' str from dual union all select '12666' str from dual union all select '16666' str from dual ) whereregexp_like(str,'16+'); --"*"表示匹配前面的子表达式零次或多次 SELECT str from ( select '167' str from dual union all select '1234567' str from dual union all select '12666' str from dual union all select '16666' str from dual ) whereregexp_like(str,'16*');

SQL> --regexp_like(src,'^A') 相当于:like 'A%' SQL> SELECT str from ( 2select 'A' str from dual union all select 'AB' str from dual union all select 'BA' str from dual union all select 'BACD' str from dual 3) 4whereregexp_like(str,'^A'); STR ---- A AB SQL> --regexp_like(src,'A$') 相当于:like '%A' SQL> SELECT str from ( 2select 'A' str from dual union all select 'AB' str from dual union all select 'BA' str from dual union all 3select 'BACD' str from dual 4) 5whereregexp_like(str,'A$'); STR ---- A BA SQL> --regexp_like(src,'^A$')相当于:like 'A' SQL> SELECT str from ( 2select 'A' str from dual union all select 'AB' str from dual union all select 'BA' str from dual union all 3select 'BACD' str from dual 4) 5whereregexp_like(str,'^A$'); STR ---- A SQL> --regexp_like(str,'^[ABC]')相当于： like '%A%' or like'%B%'or like'%C%' SQL> SELECT str 2FROM (select 'A' str from dual union all select 'AB' str from dual union all select 'BA' str from dual union all 3select 'BACD' str from dual) 4WHERE regexp_like(str,'^[ABC]'); STR ---- A AB BA BACD SQL> --regexp_like(src,'[0-9a-zA-Z]+') 相当于：like '%数字%' or like '%小写字母%' or like '%大写字母%' SQL> SELECT str 2FROM (SELECT '12345' str 3FROM dual 4UNION ALL 5SELECT 'abcde' str 6FROM dual 7UNION ALL 8SELECT '12cde' str 9FROM dual 10UNION ALL 11SELECT 'abc12' str 12FROM dual 13UNION ALL 14SELECT 'a1b2cde' str 15FROM dual 16UNION ALL 17SELECT '12345#' str 18FROM dual 19UNION ALL 20SELECT 'abcde#' str 21FROM dual) 22WHERE regexp_like(str, '^[0-9a-zA-Z]+$'); STR ------- 12345 abcde 12cde abc12 a1b2cde SQL> --"+"表示匹配前面的子表达式一次或多次 SQL> SELECT str from ( 2select '167' str from dual union all select '1234567' str from dual union all select '12666' str from dual union all select '16666' str from dual 3) 4whereregexp_like(str,'16+'); STR ------- 167 16666 SQL> --"*"表示匹配前面的子表达式零次或多次 SQL> SELECT str from ( 2select '167' str from dual union all select '1234567' str from dual union all select '12666' str from dual union all select '16666' str from dual 3) 4whereregexp_like(str,'16*'); STR ------- 167 1234567 12666 16666

4.regexp_substr regexp_substr语法:

regexp_substr(src, pattern[, pos[, occur[, match[, subexpr]]]])

regexp_substr就是substr的加强版

--用正则分隔的第一个值是192 SELECT REGEXP_SUBSTR('192.168.16.18','[^.]+',1,1,'i') AS STR FROM DUAL; --拆分IP SELECT regexp_substr(ip, '[^.]+', 1, 1) a, regexp_substr(ip, '[^.]+', 1, 2) b, regexp_substr(ip, '[^.]+', 1, 3) c, regexp_substr(ip, '[^.]+', 1, 4) d FROM (SELECT '192.168.16.18' AS ip FROM dual); --ip进行拆分 SELECT regexp_substr(ip, '[^.]+', 1, 1) a, regexp_substr(ip, '[^.]+', 1, 2) b, regexp_substr(ip, '[^.]+', 1, 3) c, regexp_substr(ip, '[^.]+', 1, 4) d FROM (SELECT '192.168.16.18' AS ip FROM dual); --用regexp_substr进行字符串行转列 --在不知道分隔符有多少的情况下,可以这么使用，但是有控制 with tmp1 as (select level lvl from dual connect by level <=10), tmp2 as (SELECT '192.168.16.18' AS ip FROM dual ) select regexp_substr(ip, '[^.]+', 1, lvl) from tmp2,tmp1; --这样可以去掉空值 with tmp2 as (SELECT '192.168.16.18' AS ip FROM dual) select regexp_substr(ip, '[^.]+', 1, level) from tmp2,dual connect bylevel <= regexp_count(ip,'\.') + 1 ;

SQL> --用正则分隔的第一个值是192 SQL> SELECT REGEXP_SUBSTR('192.168.16.18','[^.]+',1,1,'i') AS STR FROM DUAL; STR --- 192 SQL> --拆分IP SQL> SELECT regexp_substr(ip, '[^.]+', 1, 1) a, 2regexp_substr(ip, '[^.]+', 1, 2) b, 3regexp_substr(ip, '[^.]+', 1, 3) c, 4regexp_substr(ip, '[^.]+', 1, 4) d 5FROM (SELECT '192.168.16.18' AS ip FROM dual); ABCD --- --- -- -- 192 168 16 18 SQL> --ip进行拆分 SQL> SELECT regexp_substr(ip, '[^.]+', 1, 1) a, 2regexp_substr(ip, '[^.]+', 1, 2) b, 3regexp_substr(ip, '[^.]+', 1, 3) c, 4regexp_substr(ip, '[^.]+', 1, 4) d 5FROM (SELECT '192.168.16.18' AS ip FROM dual); ABCD --- --- -- -- 192 168 16 18 SQL> --用regexp_substr进行字符串行转列 SQL> --在不知道分隔符有多少的情况下,可以这么使用，但是有控制 SQL> with tmp1 as 2(select level lvl from dual connect by level <=10), 3tmp2 as 4(SELECT '192.168.16.18' AS ip FROM dual 5) 6select regexp_substr(ip, '[^.]+', 1, lvl) 7from tmp2,tmp1; REGEXP_SUBSTR(IP,'[^.]+',1,LVL ------------------------------ 192 168 16 18 10 rows selected SQL> --这样可以去掉空值 SQL> with 2tmp2 as 3(SELECT '192.168.16.18' AS ip FROM dual) 4select regexp_substr(ip, '[^.]+', 1, level) 5from tmp2,dual 6connect bylevel <= regexp_count(ip,'\.') + 1 7; REGEXP_SUBSTR(IP,'[^.]+',1,LEV ------------------------------ 192 168 16 18

5.regexp_instr regexp_instr语法:

regexp_instr(src, pattern[, pos[, occur[, retopt[, match[, subexpr]]]]])

【Oracle正则表达式简介】regexp_instr是instr的加强版本

--从第1个字符串开始查找，匹配字符','第1次出现的位置 select regexp_instr('abc,ABC,AAA,aaa',',',1,1) from dual; --从第1个字符串开始查找，匹配字符','第2次出现的位置 select regexp_instr('abc,ABC,AAA,aaa',',',1,2) from dual; --从第5个字符串开始查找，匹配字符','第2次出现的位置 select regexp_instr('abc,ABC,AAA,aaa',',',5,2) from dual; --从第1个字符串开始查找，匹配字符','第2次出现的当前位置 select regexp_instr('abc,ABC,AAA,aaa',',',1,2,0) from dual; --从第1个字符串开始查找，匹配字符','第2次出现的位置之后 select regexp_instr('abc,ABC,AAA,aaa',',',1,2,1) from dual;

SQL> --从第1个字符串开始查找，匹配字符','第1次出现的位置 SQL> select regexp_instr('abc,ABC,AAA,aaa',',',1,1) from dual; REGEXP_INSTR('ABC,ABC,AAA,AAA' ------------------------------ 4 SQL> --从第1个字符串开始查找，匹配字符','第2次出现的位置 SQL> select regexp_instr('abc,ABC,AAA,aaa',',',1,2) from dual; REGEXP_INSTR('ABC,ABC,AAA,AAA' ------------------------------ 8 SQL> --从第5个字符串开始查找，匹配字符','第2次出现的位置 SQL> select regexp_instr('abc,ABC,AAA,aaa',',',5,2) from dual; REGEXP_INSTR('ABC,ABC,AAA,AAA' ------------------------------ 12 SQL> --从第1个字符串开始查找，匹配字符','第2次出现的当前位置 SQL> select regexp_instr('abc,ABC,AAA,aaa',',',1,2,0) from dual; REGEXP_INSTR('ABC,ABC,AAA,AAA' ------------------------------ 8 SQL> --从第1个字符串开始查找，匹配字符','第2次出现的位置之后 SQL> select regexp_instr('abc,ABC,AAA,aaa',',',1,2,1) from dual; REGEXP_INSTR('ABC,ABC,AAA,AAA' ------------------------------ 9

推荐阅读

• 

  2021男生网名最新版的个性又张狂 网名2021最新版的男生网名
• 

  脚踩西瓜皮下句
• 

  自下而上的移民治理角度
• 

  爱普生c3520清零软件
• 

  19款迈锐宝xl故障率高吗? 19款迈锐宝xl故障率高吗
• 

  拜仁82巴萨阵容 拜仁82巴萨哪一年
• 

  车载充气泵怎么选 车载充气泵怎么选,车载充气泵选择方法牌介绍
• 

  花露水的成分有哪些
• 

  稻田除稗用香乐
• 

  我国最深的湖泊是 中国最深的湖是
• 

  csgo比赛回放在哪看 csgo比赛回放怎么看
• 

  封控区怎样做核酸检测
• 

  中国数学家名言名句 数学家名言名句
• 

  为什么新水壶烧水壶底有斑点
• 

  生紫烟的生怎么解释 生紫烟的生是什么意思
• 

  猝死|24岁小伙高温天打球后猝死：好端端的运动，为何会有猝死的意外？
• 

  提肛运动对痔疮好吗
• 

  香菇没煮熟吃了会中毒吗
• 

  农历阳历的区别
• 

  农民起义哪次是洪秀全领导的 洪秀全领导的是什么农民起义

• Python爬虫|Python爬虫 --- 1.4 正则表达式（re库）
• oracle|oracle java jdk install
• 数据技术|一文了解Gauss数据库（开发历程、OLTP&OLAP特点、行式&列式存储，及与Oracle和AWS对比）
• 正则匹配
• 正则表达式中增加变量
• sed及正则表达式
• Java8|Java8 Collections.sort()及Arrays.sort()中Lambda表达式及增强版Comparator的使用
• ORACLE|ORACLE 12C ADG 之三A (DG 配置管理)
• Java正则表达式的应用
• oracle储存过程