Oracle正则表达式使用-使用篇(三)

正则表达式功能

/*
   解析一下几个参数的含义：
  1、source_char，输入的字符串，可以是列名或者字符串常量、变量。
  2、pattern，正则表达式。
  3、match_parameter，匹配选项。
  取值范围：
  i：大小写不敏感；
  c：大小写敏感；
  n：点号
  . 不匹配换行符号；
  m：多行模式；
  x：扩展模式，忽略正则表达式中的空白字符。
  4、position，标识从第几个字符开始正则表达式匹配。
  5、occurrence，标识第几个匹配组。
  6、replace_string，替换的字符串。
*/

1.regexp_count

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

--regexp_count语法
regexp_count(src, pattern[, pos[, match]])

1.1示例：

--匹配字符串中某个字符出现的次数 结果：4
SELECT REGEXP_COUNT('M01-M02-M03-M04-M05', '-') AS cot FROM DUAL;

--遇到通配符  要使用  \  转义 结果：2
SELECT REGEXP_COUNT('M1#,B2&,C3$,D4$,E5', '\$,') AS cot FROM DUAL;

--从第10个字符串开始匹配 结果：3
SELECT REGEXP_COUNT('aaa,bbb,ccc,ddd,eee', ',', 6) AS cot FROM DUAL;

--默认是区分大小写的匹配 结果：4
SELECT REGEXP_COUNT('abc,ABC,AAA,abaa', 'a') AS cot FROM DUAL;

--设置成不区分大小写的匹配 结果：8
SELECT REGEXP_COUNT('abc,ABC,AAA,aaa', 'a', 1, 'i') AS cot FROM DUAL;

2.regexp_replace

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

--regexp_replace语法:
regexp_replace(src, pattern[, replace_str[, pos[, occur[, match]]]])

2.1示例

--去掉字符串中的MY_字符
select regexp_replace('MY_ORACLE', '[MY_]') from dual;

--去掉字符串中的非MY_字符
select regexp_replace('MY_ORACLE','[^MY_]') from dual;

--从第1个字符开始去掉字符串中的MY字符
select regexp_replace('MY_ORACLE','[MY]','',1) from dual;

--从第1个字符开始去掉第二次出现字符串中的MY字符
select regexp_replace('MY_ORACLE_MY_ORACLE','[Y]','',1,2) from dual;

--取名字中的大写字母
SELECT regexp_replace(data, '([[:upper:]])(.*)([[:upper:]])(.*)', '\1...\3')
  FROM (SELECT 'Zhao Da' data
          FROM dual
        UNION ALL
        SELECT 'LI Si' data
          FROM dual
        UNION ALL
        SELECT 'Wang Wu' data
          FROM dual);

3.regexp_like

--regexp_like语法:
regexp_like(src, pattern[, match])

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

3.1示例

--regexp_like(src,'^M') 相当于:like 'M%'
SELECT str
  from (select 'C001' str
          from dual
        union all
        select 'M002' str
          from dual
        union all
        select 'M003' str
          from dual
        union all
        select 'F004' str from dual)
 where regexp_like(str, '^M');

--regexp_like(src,'M$') 相当于:like '%M'
SELECT str
  from (select 'C001M' str
          from dual
        union all
        select 'M002M' str
          from dual
        union all
        select 'M003' str
          from dual
        union all
        select 'F004' str from dual)
 where regexp_like(str, 'M$');

--regexp_like(src,'^M$')相当于:like 'M'
SELECT str
  from (select 'M' str
          from dual
        union all
        select 'M002M' str
          from dual
        union all
        select 'M003M' str
          from dual
        union all
        select 'F004' str from dual)
 where regexp_like(str, '^M$');

--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 '1666166' str from dual)
 where regexp_like(str, '26*');

4.regexp_substr

--regexp_substr语法:
regexp_substr(src, pattern[, pos[, occur[, match[, subexpr]]]])

regexp_substr就是substr的加强版

4.1示例：

--用正则分隔的第一个值是127
SELECT REGEXP_SUBSTR('127.0.0.1','[^.]+',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 '127.0.0.1' AS ip FROM dual);
  
--用regexp_substr进行字符串行转列
--在不知道分隔符有多少的情况下,可以这么使用，但是有控制
with tmp1 as
(select level lvl from dual connect by level <=5),
tmp2 as
(SELECT '127.0.0.1' AS ip FROM dual
)
select regexp_substr(ip, '[^.]+', 1, lvl)
from tmp2,tmp1;

--这样可以去掉空值
with 
tmp2 as
(SELECT '127.0.0.1' AS ip FROM dual)
select regexp_substr(ip, '[^.]+', 1, level)
from tmp2,dual
connect by  level <= regexp_count(ip,'\.') + 1
;

5.regexp_instr

--regexp_instr语法:
regexp_instr(src, pattern[, pos[, occur[, retopt[, match[, subexpr]]]]])

regexp_instr是instr的加强版本

5.1示例
 

--从第1个字符串开始查找，匹配字符'-'第1次出现的位置
select regexp_instr('55510327-8bb6-447b-a25e-352422572d21','-',1,1) from dual;

--从第1个字符串开始查找，匹配字符'-'第2次出现的位置
select regexp_instr('55510327-8bb6-447b-a25e-352422572d21','-',1,2) from dual;

--从第5个字符串开始查找，匹配字符'-'第2次出现的位置
select regexp_instr('55510327-8bb6-447b-a25e-352422572d21','-',5,2) from dual;

--从第1个字符串开始查找，匹配字符'-'第2次出现的当前位置
select regexp_instr('55510327-8bb6-447b-a25e-352422572d21','-',1,2,0) from dual;

--从第1个字符串开始查找，匹配字符'-'第2次出现的位置之后
select regexp_instr('55510327-8bb6-447b-a25e-352422572d21','-',1,2,1) from dual;
--获取GUID
select sys_guid() from dual;
--Oracle 生成带''-"的Guid
SELECT SUBSTR(GUID, 1, 8) || '-' || SUBSTR(GUID, 9, 4) || '-' ||
       SUBSTR(GUID, 13, 4) || '-' || SUBSTR(GUID, 17, 4) || '-' ||
       SUBSTR(GUID, 20, 12) GUID
  FROM (SELECT SYS_GUID() GUID FROM DUAL)