Oracle通过一个字段的值将一条记录拆分为多条记录

前言

之前遇到了一次这样的需求,当时没有记录,这一次又赶上了,简单的记录一下。

场景

表A中存放了集装箱的信息,一个集装箱一条记录,表B中存放了对于集装箱操作的指令,一条指令包括多个集装箱箱号,通过分号;切割(TCIU2347687;XUTR3546865),现在的需求是,对于已经在指令表B中的集装箱,在查询表A时需要过滤掉。

  • 很容易想到的是not in, 然而分号分割。
  • 其次,not like,然而[Err] ORA-01427: 单行子查询返回多个行,表示like后面只接受模糊查询的单个值。

所以必须将分号分割的记录,拆分成单独的记录。
单个记录
变成:
切割后

实现

Oracle可使用regexp_substr函数实现,实现上面切割的sql为:

select regexp_substr('TCIU2347687;XUTR3546865', '[^;]+', 1, level) JZXXH
from dual connect by level <= regexp_count('TCIU2347687;XUTR3546865', ';') + 1

其中regexp_substr各个参数的含义:

  • TCIU2347687;XUTR3546865 表示需要分割匹配的串(我这里只是做了示例,真实情况下是表的字段)。
  • [^;]+典型的正则表达式,我这里分号切割,因此确定分割规则是多个不是分号的字符,因此遇到分号便结束,完成一个串的获取。
  • 1开始位置,最左端(Oracle下标都是1开始
  • level表示第几个匹配上的。
    为了直观点搞清楚这个函数,比如下面的语句:
select REGEXP_SUBSTR('aaa;bbb','[^;]+',1,1) AS STR FROM dual; 

结果就是aaa, 如果把第二个1变成2,输出就是bbb
好了,这部分意图很明显了,下面就是把它每一个切割串取出来,看到上面取level个,而这个level是个什么东西呢,在这个之前,先看regexp_count(string, c)函数,这个函数其实很好理解,返回string中c的个数。
然后就是这个level,这是一个伪列,和RowNum相似,

SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=2; 

level
所以再回到最初的sql,也就很好理解了。


最后

  • 本文内容个人拙见,若有出入,欢迎指正。
  • 欢迎赏脸关注:家佳Talk

欢迎赏脸关注:家佳Talk

posted @ 2019-08-16 18:00  小小范同学  阅读(7820)  评论(0编辑  收藏  举报