Oracle正则表达式简介

最新推荐文章于 2023-04-24 21:00:28 发布

只是甲

最新推荐文章于 2023-04-24 21:00:28 发布

阅读量3k

点赞数 1

分类专栏： # Oracle开发 Oracle循序渐进文章标签： regex 正则表达式 sql oracle

本文链接：https://blog.csdn.net/u010520724/article/details/106032424

版权

Oracle循序渐进同时被 2 个专栏收录

15 篇文章 6 订阅

订阅专栏

Oracle开发

12 篇文章 5 订阅

订阅专栏

文章目录

备注:测试数据库版本为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')
  2    FROM (SELECT 'Bill Gates' data
  3            FROM dual
  4          UNION ALL
  5          SELECT 'Michael Jordan' data
  6            FROM dual
  7          UNION ALL
  8          SELECT 'Kobe Bryant' data
  9            FROM 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
)
where  regexp_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
)
where  regexp_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
)
where  regexp_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
)
where  regexp_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
)
where  regexp_like(str,'16*');

SQL> --regexp_like(src,'^A') 相当于:like 'A%'
SQL> SELECT str from (
  2  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
  3  )
  4  where  regexp_like(str,'^A');
STR
----
A
AB
SQL> --regexp_like(src,'A$') 相当于:like '%A'
SQL> SELECT str from (
  2  select 'A' str from dual union all select 'AB' str from dual union all select 'BA' str from dual union all
  3  select 'BACD' str from dual
  4  )
  5  where  regexp_like(str,'A$');
STR
----
A
BA
SQL> --regexp_like(src,'^A$')相当于:like 'A'
SQL> SELECT str from (
  2  select 'A' str from dual union all select 'AB' str from dual union all select 'BA' str from dual union all
  3  select 'BACD' str from dual
  4  )
  5  where  regexp_like(str,'^A$');
STR
----
A
SQL> --regexp_like(str,'^[ABC]')相当于： like '%A%' or like'%B%'  or like'%C%'
SQL> SELECT str
  2    FROM (select 'A' str from dual union all select 'AB' str from dual union all select 'BA' str from dual union all
  3  select 'BACD' str from dual)
  4   WHERE 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
  2    FROM (SELECT '12345' str
  3            FROM dual
  4          UNION ALL
  5          SELECT 'abcde' str
  6            FROM dual
  7          UNION ALL
  8          SELECT '12cde' str
  9            FROM dual
 10          UNION ALL
 11          SELECT 'abc12' str
 12            FROM dual
 13          UNION ALL
 14          SELECT 'a1b2cde' str
 15            FROM dual
 16          UNION ALL
 17          SELECT '12345#' str
 18            FROM dual
 19          UNION ALL
 20          SELECT 'abcde#' str
 21            FROM dual)
 22   WHERE regexp_like(str, '^[0-9a-zA-Z]+$');
STR
-------
12345
abcde
12cde
abc12
a1b2cde
SQL> --"+"表示匹配前面的子表达式一次或多次
SQL> SELECT str from (
  2  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
  3  )
  4  where  regexp_like(str,'16+');
STR
-------
167
16666
SQL> --"*"表示匹配前面的子表达式零次或多次
SQL> SELECT str from (
  2  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
  3  )
  4  where  regexp_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 by  level <= 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,
  2         regexp_substr(ip, '[^.]+', 1, 2) b,
  3         regexp_substr(ip, '[^.]+', 1, 3) c,
  4         regexp_substr(ip, '[^.]+', 1, 4) d
  5    FROM (SELECT '192.168.16.18' AS ip FROM dual);
A   B   C  D
--- --- -- --
192 168 16 18
SQL> --ip进行拆分
SQL> SELECT regexp_substr(ip, '[^.]+', 1, 1) a,
  2         regexp_substr(ip, '[^.]+', 1, 2) b,
  3         regexp_substr(ip, '[^.]+', 1, 3) c,
  4         regexp_substr(ip, '[^.]+', 1, 4) d
  5    FROM (SELECT '192.168.16.18' AS ip FROM dual);
A   B   C  D
--- --- -- --
192 168 16 18
SQL> --用regexp_substr进行字符串行转列
SQL> --在不知道分隔符有多少的情况下,可以这么使用，但是有控制
SQL> with tmp1 as
  2  (select level lvl from dual connect by level <=10),
  3  tmp2 as
  4  (SELECT '192.168.16.18' AS ip FROM dual
  5  )
  6  select regexp_substr(ip, '[^.]+', 1, lvl)
  7  from tmp2,tmp1;
REGEXP_SUBSTR(IP,'[^.]+',1,LVL
------------------------------
192
168
16
18
10 rows selected
SQL> --这样可以去掉空值
SQL> with
  2  tmp2 as
  3  (SELECT '192.168.16.18' AS ip FROM dual)
  4  select regexp_substr(ip, '[^.]+', 1, level)
  5  from tmp2,dual
  6  connect by  level <= 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]]]]])

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