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 用法相同，
但是它们使用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,},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,-23','[^,]+',1,'i') AS STR FROM DUAL;  
    --结果是：34   
    --2、查询使用正则分割后的最后一个值，也就是-23  
    SELECT REGEXP_SUBSTR('34,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,LEVEL,'i'),'NULLL') AS STR FROM DUAL CONNECT BY LEVEL <= 7;  
    --结果是：  
    --34  
    --56  
    ---23  
    --null  
    --null  
    --mull  
    --null  
    --5、优化上面的sql语句，让生成的行的数量符合实际情况  
    SELECT REGEXP_SUBSTR('34,'i') AS STR FROM DUAL CONNECT BY LEVEL <= LENGTH('34,-23') - LENGTH(REGEXP_REPLACE('34,','')) + 1;  
    --结果是：  
    --34  
    --56  
    ---23

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

select T_PERSON_ID,T_PERSON_NAME,regexp_substr(T_TYPE_ID,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,lv) + 1,lv + 1) -
   (instr(tempgroups,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,15' as workgroups
    from dual
  union
  select '1002' as userid,lv + 1) -
    (instr(tempgroups,' as tempgroups,') -
     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,--regexp_substr(str,reg,起始位置 第几次)
  regexp_substr(workgroups,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

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

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

猜你在找的Oracle相关文章