oracle正则函数，regexp_substr，按分隔符单行转多行

最新推荐文章于 2023-11-09 10:30:23 发布

C211的葡萄

最新推荐文章于 2023-11-09 10:30:23 发布

阅读量2.9k

点赞数 1

文章标签： oracle 数据库正则

/*
ORACLE中的支持正则表达式的函数主要有下面四个：
1，REGEXP_LIKE ：与LIKE的功能相似
2，REGEXP_INSTR ：与INSTR的功能相似
3，REGEXP_SUBSTR ：与SUBSTR的功能相似
4，REGEXP_REPLACE ：与REPLACE的功能相似
它们在用法上与Oracle SQL 函数LIKE、INSTR、SUBSTR 和REPLACE 用法相同，
但是它们使用POSIX 正则表达式代替了老的百分号（%）和通配符（_）字符。
POSIX 正则表达式由标准的元字符（metacharacters）所构成：
'^' 匹配输入字符串的开始位置，在方括号表达式中使用，此时它表示不接受该字符集合。
'$' 匹配输入字符串的结尾位置。如果设置了 RegExp 对象的 Multiline 属性，则 $ 也匹
配 ' ' 或 ' '。
'.' 匹配除换行符之外的任何单字符。
'?' 匹配前面的子表达式零次或一次。
'+' 匹配前面的子表达式一次或多次。
'*' 匹配前面的子表达式零次或多次。
'|' 指明两项之间的一个选择。例子'^([a-z]+|[0-9]+)$'表示所有小写字母或数字组合成的
字符串。
'( )' 标记一个子表达式的开始和结束位置。
'[]' 标记一个中括号表达式。
'{m,n}' 一个精确地出现次数范围，m=<出现次数<=n，'{m}'表示出现m次，'{m,}'表示至少
出现m次。
um 匹配 num，其中 num 是一个正整数。对所获取的匹配的引用。
字符簇： 
[[:alpha:]] 任何字母。
[[:digit:]] 任何数字。
[[:alnum:]] 任何字母和数字。
[[:space:]] 任何白字符。
[[:upper:]] 任何大写字母。
[[:lower:]] 任何小写字母。
[[:punct:]] 任何标点符号。
[[:xdigit:]] 任何16进制的数字，相当于[0-9a-fA-F]。
各种操作符的运算优先级
\转义符
(), (?:), (?=), [] 圆括号和方括号
*, +, ?, {n}, {n,}, {n,m} 限定符
^, $, anymetacharacter 位置和顺序
| 
*/

--创建表
create table fzq
(
  id varchar(4),
  value varchar(10)
);
--数据插入
insert into fzq values
('1','1234560');
insert into fzq values
('2','1234560');
insert into fzq values
('3','1b3b560');
insert into fzq values
('4','abc');
insert into fzq values
('5','abcde');
insert into fzq values
('6','ADREasx');
insert into fzq values
('7','123  45');
insert into fzq values
('8','adc  de');
insert into fzq values
('9','adc,.de');
insert into fzq values
('10','1B');
insert into fzq values
('10','abcbvbnb');
insert into fzq values
('11','11114560');
insert into fzq values
('11','11124560');
--regexp_like
--查询value中以1开头60结束的记录并且长度是7位
select * from fzq where value like '1____60';
select * from fzq where regexp_like(value,'1....60');
--查询value中以1开头60结束的记录并且长度是7位并且全部是数字的记录。
--使用like就不是很好实现了。
select * from fzq where regexp_like(value,'1[0-9]{4}60');
-- 也可以这样实现，使用字符集。
select * from fzq where regexp_like(value,'1[[:digit:]]{4}60');
-- 查询value中不是纯数字的记录
select * from fzq where not regexp_like(value,'^[[:digit:]]+$');
-- 查询value中不包含任何数字的记录。
select * from fzq where regexp_like(value,'^[^[:digit:]]+$');
--查询以12或者1b开头的记录.不区分大小写。
select * from fzq where regexp_like(value,'^1[2b]','i');
--查询以12或者1b开头的记录.区分大小写。
select * from fzq where regexp_like(value,'^1[2B]');
-- 查询数据中包含空白的记录。
select * from fzq where regexp_like(value,'[[:space:]]');
--查询所有包含小写字母或者数字的记录。
select * from fzq where regexp_like(value,'^([a-z]+|[0-9]+)$');
--查询任何包含标点符号的记录。
select * from fzq where regexp_like(value,'[[:punct:]]');
/*
理解它的语法就可以了。其它的函数用法类似。

  1*  select regexp_substr('1234-23-45','[[:alnum:]]+') from dual
SQL> /

REGE
----

1234
  1*  select regexp_substr('1234abc-23-45','[[:alnum:]]+') from dual
SQL> /

REGEXP_
-------
1234abc

  1*  select regexp_substr('1234abc-23-45','[[:alnum:]]+') from dual
SQL> /

REGEXP_
-------
1234abc

select REGEXP_SUBSTR('123-456-7890', '-[^-]+-' )
"REGEXP_SUBSTR"
from DUAL;
REGEX
-----
-456-

Oracle中如何使用REGEXP_SUBSTR函数
REGEXP_SUBSTR函数格式如下：

function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)
__srcstr ：需要进行正则处理的字符串
__pattern ：进行匹配的正则表达式
__position ：起始位置，从第几个字符开始正则表达式匹配（默认为1）
__occurrence ：标识第几个匹配组，默认为1
__modifier ：模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。）

实际应用如下：在oracle中，使用一条语句实现将'34,56,-23'拆分成'34'，'56'，'-23'的集合。

    --1、查询使用正则分割后的第一个值，也就是34  
    SELECT REGEXP_SUBSTR('34,56,-23','[^,]+',1,1,'i') AS STR FROM DUAL;  
    --结果是：34   
    --2、查询使用正则分割后的最后一个值，也就是-23  
    SELECT REGEXP_SUBSTR('34,56,-23','[^,]+',1,3,'i') AS STR FROM DUAL;  
    --结果是：-23  
    --3、获取一个多个数值的列，从而能够让结果以多行的形式展示出来  
    SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=7;  
    --结果是：  
    --1  
    --2  
    --3  
    --4  
    --5  
    --6  
    --7  
    --4、将上面REGEXP_SUBSTR的occurrence关联  
    SELECT NVL(REGEXP_SUBSTR('34,56,-23', '[^,]+', 1, LEVEL, 'i'), 'NULLL') AS STR FROM DUAL CONNECT BY LEVEL <= 7;  
    --结果是：  
    --34  
    --56  
    ---23  
    --null  
    --null  
    --mull  
    --null  
    --5、优化上面的SQL语句，让生成的行的数量符合实际情况  
    SELECT REGEXP_SUBSTR('34,56,-23', '[^,]+', 1, LEVEL, 'i') AS STR FROM DUAL CONNECT BY LEVEL <= LENGTH('34,56,-23') - LENGTH(REGEXP_REPLACE('34,56,-23', ',', '')) + 1;  
    --结果是：  
    --34  
    --56  
    ---23

一字段含有逗号分隔的串，如何把这条记录按分隔符分成多条记录

select T_PERSON_ID,
       T_PERSON_NAME,
       regexp_substr(T_TYPE_ID, '[^,]+', 1, level) T_TYPE_ID
  from t_info
connect by level <= regexp_count(T_TYPE_ID, ',') + 1
       and T_PERSON_ID = prior T_PERSON_ID
       and prior dbms_random.value is not null;

oracle简单按分隔符单行转多行sql

今天看到篇博客， http://www.cnblogs.com/hankuikui/p/3523013.html ，说的是按分隔符把一列转成多行，原文给出的方法是：

with tmp_t as
 (select '1001' as userId, '10,12,15' as workgroups
    from dual
  union
  select '1002' as userId, '2,4,5' as workgroups from dual)
select userid,
       substr(tempgroups,
   instr(tempgroups, ',', 1, lv) + 1,
   instr(tempgroups, ',', 1, lv + 1) -
   (instr(tempgroups, ',', 1, lv) + 1))
  from (select userid,
    ',' || workgroups || ',' AS tempgroups,
    length(workgroups || ',') -
    nvl(length(replace(workgroups, ',')) ， 0) AS groupcount
          FROM tmp_t) a,
       (select LEVEL lv from dual CONNECT BY LEVEL <= 5) b
 where b.lv <= a.groupcount
 order by userid, lv

结果为：

使用了CONNECT BY 构造1-5的lv值和instr截取，详细分析见原文。

个人看到connect by level <=5总想这替换掉5，只要得到表中最大的,个数就可以了，如下：

with tmp_t as
 (select '1001' as userid, '10,12,15' as workgroups
    from dual
  union
  select '1002' as userid, '2,4,5' as workgroups from dual)
select userid,
  substr(tempgroups,
    instr(tempgroups, ',', 1, lv) + 1,
    instr(tempgroups, ',', 1, lv + 1) -
    (instr(tempgroups, ',', 1, lv) + 1))
  from (select userid,
     ',' || workgroups || ',' as tempgroups,
     length(workgroups || ',') -
     nvl(length(replace(workgroups, ',')) ， 0) as groupcount
     from tmp_t) a,
  (select level lv
     from dual
   connect by level <= (select max(length(workgroups) -
               length(replace(workgroups, ',', ''))) + 1
           from tmp_t)) b
 where b.lv <= a.groupcount
 order by userid, lv

上面的写法简单易懂，恰巧昨天看到篇文章，里面有更好的写法，原文链接： http://www.dewen.org/q/16184/%E4%BB%A5%E5%A4%8D%E6%9D%82%E6%95%B0%E6%8D%AE%E5%AD%97%E6%AE%B5%E5%88%86%E7%BB%84%E6%9F%A5%E8%AF%A2

更好的写法如下：

with tmp_t as
 (select '1001' as userid, '10,12,15' as workgroups
    from dual
  union
  select '1002' as userid, '2,4,5' as workgroups from dual)
select userid,
  --regexp_substr(str,reg,起始位置 第几次)
  regexp_substr(workgroups, '[^,]+', 1, level) clr
  from tmp_t
connect by level <= regexp_count(workgroups, ',') + 1
  --regexp_count(workgroups, ',') 统计字符串中,的数量
  --用法参考http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions135.htm#SQLRF51665
  and userid = prior userid
  and prior dbms_random.value is not null

相当简洁，很不错的写法，结果和上面的一样。

全文完。

C211的葡萄

关注

1
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
oracle正则函数，regexp_substr，按分隔符单行转多行

/*ORACLE中的支持正则表达式的函数主要有下面四个：1，REGEXP_LIKE ：与LIKE的功能相似2，REGEXP_INSTR ：与INSTR的功能相似3，REGEXP_SUBSTR ：与SUBSTR的功能相似4，REGEXP_REPLACE ：与REPLACE的功能相似它们在用法上与Oracle SQL 函数LIKE、INSTR、SUBSTR 和REPLACE 用法相同，但
复制链接

扫一扫