Oracle正则表达式函数

Oracle使用正则表达式的4个主要函数：

1、regexp_like 只能用于条件表达式，和 like 类似，但是使用的正则表达式进行匹配，语法很简单：

2、regexp_substr 函数，和 substr 类似，用于拾取合符正则表达式描述的字符子串，语法如下：

3、regexp_instr 函数，和 instr 类似，用于标定符合正则表达式的字符子串的开始位置，语法如下：

4、regexp_replace 函数，和 replace 类似，用于替换符合正则表达式的字符串，语法如下：

这里解析一下几个参数的含义：

1、source_char，输入的字符串，可以是列名或者字符串常量、变量。

2、pattern，正则表达式。

3、match_parameter，匹配选项。

取值范围： i：大小写不敏感； c：大小写敏感；n：点号 . 不匹配换行符号；m：多行模式；x：扩展模式，忽略正则表达式中的空白字符。

4、position，标识从第几个字符开始正则表达式匹配。

5、occurrence，标识第几个匹配组。

6、replace_string，替换的字符串。

示例：

基础数据准备

-- 创建表及测试数据
create table tmp (
  ID varchar2(60),
  STR varchar2(60)
)

insert into tmp values ('like','a9999');
insert into tmp values ('like','a9c');
insert into tmp values ('like','A7007');
insert into tmp values ('like','123a34cc');
insert into tmp values ('substr','123,234,345');
insert into tmp values ('substr','12,34.56:78');
insert into tmp values ('substr','123456789');
insert into tmp values ('instr','192.168.0.1');
insert into tmp values ('replace','(020)12345678');
insert into tmp values ('replace','001517729C28');

-- 查询插入结果
SQL> select count(*) from tmp;

  COUNT(*)
----------
        10

regexp_like

-- regexp_like示例
-- 'i'忽略大小写
SQL> SELECT str from tmp where id='like' and regexp_like(str,'A\d+','i');

STR
------------------------------------------------------------
a9999
a9c
A7007
123a34cc

-- 不带'c'
SQL> select str from tmp where id='like' and regexp_like(str, 'a\d+');

STR
------------------------------------------------------------
a9999
a9c
123a34cc

-- 带'c'
SQL> SELECT str from tmp where id='like' and regexp_like(str,'A\d+','c');    

STR
--------------------
A7007

-- 匹配以a开头的
SQL> select str from tmp where id='like' and regexp_like(str,'^a\d+');

STR
------------------------------------------------------------
a9999
a9c

-- 匹配以a开头，以数字结尾的
SQL> SELECT str from tmp where id='like' and regexp_like(str,'^a\d+$');

STR
------------------------------------------------------------
a9999

regexp_substr

-- regexp_substr示例1
SELECT
str,
regexp_substr(str,'[^,]+')     str_1_1,
regexp_substr(str,'[^,]+',1,1) str_1_1,
regexp_substr(str,'[^,]+',1,2) str_1_2,  -- occurrence 第几个匹配组
regexp_substr(str,'[^,]+',2,1) str_2_1   -- position 从第几个字符开始匹配
from tmp
where id='substr';

查询结果：

-- regexp_substr示例2
SQL> col str format a20
SQL> SELECT
  2  STR,
  3  REGEXP_SUBSTR(STR, '\d') STR,
  4  REGEXP_SUBSTR(STR, '\d+', 1, 1) STR,
  5  REGEXP_SUBSTR(STR, '\d{2}', 1, 2) STR,
  6  REGEXP_SUBSTR(STR, '\d{3}', 2, 1) STR
  7  FROM TMP
  8  WHERE ID = 'substr';

查询结果：

regexp_instr

略，以后补充。

regexp_replace

-- regexp_replace示例
SELECT STR,
REGEXP_REPLACE(STR, '020', 'GZ') STR,
REGEXP_REPLACE(STR, '(\d{3})(\d{3})', '<\2\1>') STR -- 将第一、第二捕获组交换位置，用尖括号标识出来
FROM TMP
WHERE ID = 'replace';

查询结果：

综合示例

-- 建表
create table tmp1(
id varchar2(60),
str varchar2(512)
);

-- 插入测试数据
insert into tmp1 values('test','020000080568179234090000010030040050040205090070080040050000060289634175010000020');

-- 使用regex函数处理数据
SQL> select str,REGEXP_SUBSTR(str,'\d{9}') as row_line from tmp1; 

STR                  ROW_LINE
02000008056817923409 020000080
00000100300400500402
05090070080040050000
060289634

-- 未完待续

查询结果：

参考资源：
https://www.cnblogs.com/suinlove/p/3981454.html

https://docs.oracle.com/database/121/SQLRF/conditions007.htm#SQLRF00501