ORACLE逗号分割的字符串转多行

本文介绍如何使用Oracle的REGEXP_SUBSTR和REGEXP_COUNT函数来分割包含逗号分隔值的字符串,通过示例展示了如何将单行或多行数据正确地分割成多个独立的值,避免了因Connect By层级递归导致的数据重复问题。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

分割单行数据

我们需要使用REGEXP_SUBSTRREGEXP_COUNT两个ORACLE函数

function REGEXP_SUBSTR(string, pattern, position, occurrence, modifier)
string:需要进行正则处理的字符串
pattern:进行匹配的正则表达式
position:起始位置,从第几个字符开始正则表达式匹配(默认为1)
occurrence:标识第几个匹配组,默认为1
modifier:模式(‘i’不区分大小写进行检索;‘c’区分大小写进行检索。默认为’c’)

function REGEXP_COUNT ( string, pattern [, position [, match_param]])
返回pattern 在string串中出现的次数。如果未找到匹配,则函数返回0。position 变量告诉Oracle 在源串的什么位置开始搜索。在开始位置之后每出现一次模式,都会使计数结果增加1。

SELECT REGEXP_SUBSTR('B00053,D00058,D00094', '[^,]+', 1, LEVEL) VALUE
  FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT('B00053,D00058,D00094', '[^,]+')

分割后结果如下:
在这里插入图片描述

分割多行数据

我们有三行初始数据
在这里插入图片描述

WITH TEMP AS
 (SELECT T.VALUE, T.COUNTS, T.EXE_ID
    FROM SC_MONIT_EXERESULT T
   INNER JOIN SYS_TASK_EXECUTE A
      ON T.EXE_ID = A.EXEC_ID
   WHERE FIELD_CODE = 'fundList'
     AND A.BUSIDATE = '20180604'
     AND VALUE = 'B00053,D00058,D00094')
SELECT REGEXP_SUBSTR(VALUE, '[^,]+', 1, LEVEL) VALUE,
       COUNTS,
       EXE_ID
  FROM TEMP
CONNECT BY LEVEL <= REGEXP_COUNT(VALUE, '[^,]+')

分割后数据变成了39行而不是9行!
问题出在使用connect by时,没有类似 id=prior pid的条件,而是 connect by rownum<xxx 或 connect by level<xxx 时,每一条记录都会作为自己或者其他记录的子节点,也就说,每一条记录的子节点就是表上所有的记录。而树的层数就是rownum(或是level)值。
假设表中有N条记录,则记F(N,l)为select id,level from t connect by level<l 的结果集数目。那么:
F ( N , 1 ) = N F(N,1)=N F(N,1)=N
F ( N , l ) = F ( N , l − 1 ) ∗ N + N F(N,l) = F(N,l-1)*N+N F(N,l)=F(N,l1)N+N
l e v e l &lt; = 3 level&lt;=3 level<=3 时,递归查询到的树状结构:
在这里插入图片描述
参考文章:深入理解connect by level

我们需要给树加上父子关系,用rownum作为PK列

WITH TEMP AS
 (SELECT T.VALUE, T.COUNTS, T.EXE_ID, ROWNUM ROWNUM1
    FROM SC_MONIT_EXERESULT T
   INNER JOIN SYS_TASK_EXECUTE A
      ON T.EXE_ID = A.EXEC_ID
   WHERE FIELD_CODE = 'fundList'
     AND A.BUSIDATE = '20180604'
     AND VALUE = 'B00053,D00058,D00094')
SELECT 'fundCode' FIELD_CODE,
       REGEXP_SUBSTR(VALUE, '[^,]+', 1, LEVEL) VALUE,
       COUNTS,
       EXE_ID
  FROM TEMP
CONNECT BY PRIOR ROWNUM1 = ROWNUM1
       AND LEVEL <= REGEXP_COUNT(VALUE, '[^,]+')
       AND PRIOR DBMS_RANDOM.VALUE() IS NOT NULL

PRIOR DBMS_RANDOM.VALUE() IS NOT NULL告诉ORACLE每次循环是不一样的,不然会报connect by死循环
最后得到的结果:
在这里插入图片描述

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值