oracle根据字符串切割成行

最近做数据迁移,老库中某个字段关联其他表时存储的是逗号分隔的数据,迁移到新库中,需要将这些逗号分隔的数据拆分存成多行,从网上找来的sql示例,却发现有问题;

网上示例:

select regexp_substr('1,2,3','[^,]+',1,level,'i') split from dual 
connect by level <= (length('1,2,3') -length(regexp_replace('1,2,3',',',''))+1);

   该sql运行结果确实达到了预期

 但是实际运用中却遇到问题

select ITEM_ID,AGREEMENTS,regexp_substr(AGREEMENTS,'[^,]+',1,level,'i') split from (
SELECT '1,2,3,4,5,6' AS AGREEMENTS,
10080 AS ITEM_ID FROM DUAL
UNION ALL SELECT '1,10,9,8,1792' AS AGREEMENTS,
1184 AS ITEM_ID FROM DUAL
UNION ALL SELECT '10,1' AS AGREEMENTS,
10083 AS ITEM_ID FROM DUAL
UNION ALL SELECT '1,2,3,6,4,16' AS AGREEMENTS,
10087 AS ITEM_ID FROM DUAL
)
connect by level <= length(AGREEMENTS) -length(regexp_replace(AGREEMENTS,',',''))+1
order by ITEM_ID;

数据多达一千多行,与预期不符,实际运用中应该稍加改造才能达到预期,但是网上很少有这段的改造,故发布该文,希望帮助需要用到的朋友

select ITEM_ID,AGREEMENTS,regexp_substr(AGREEMENTS,'[^,]+',1,level,'i') split from (
SELECT '1,2,3,4,5,6' AS AGREEMENTS,
10080 AS ITEM_ID FROM DUAL
UNION ALL SELECT '1,10,9,8,1792' AS AGREEMENTS,
1184 AS ITEM_ID FROM DUAL
UNION ALL SELECT '10,1' AS AGREEMENTS,
10083 AS ITEM_ID FROM DUAL
UNION ALL SELECT '1,2,3,6,4,16' AS AGREEMENTS,
10087 AS ITEM_ID FROM DUAL
)
connect by level <= length(AGREEMENTS) -length(regexp_replace(AGREEMENTS,',',''))+1
AND ITEM_ID = PRIOR ITEM_ID
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
order by ITEM_ID;

预期结果:

另外这里再分享这个切割的升级版:切割成行之后有时需要再次加工,将其在现有的类型中分配不同类型,以下是代码示例

SELECT M.*,
  REPLACE(REPLACE(SUBSTR('-1,-5,-X,-6,40,41,',3*(RN-1)+1,3),'-',''),',','') TYPE
  FROM (
  SELECT ITEM_ID,
  AGREEMENTS,
  REGEXP_SUBSTR(AGREEMENTS, '[^,]+', 1, LEVEL) AGREEMENT_NO,
  ROW_NUMBER () OVER (
  PARTITION BY ITEM_ID ORDER BY TO_NUMBER(ITEM_ID) ASC
  ) AS RN
  FROM (
  SELECT '1,2,3,4,5,6' AS AGREEMENTS,
  10080 AS ITEM_ID FROM DUAL
  UNION ALL SELECT '1,10,9,8,1792' AS AGREEMENTS,
  1184 AS ITEM_ID FROM DUAL
  UNION ALL SELECT '10,1' AS AGREEMENTS,
  10083 AS ITEM_ID FROM DUAL
  UNION ALL SELECT '1,2,3,6,4,16' AS AGREEMENTS,
  10087 AS ITEM_ID FROM DUAL
  ) T
  connect by level <= length(AGREEMENTS) -length(regexp_replace(AGREEMENTS,',',''))+1
  AND ITEM_ID = PRIOR ITEM_ID
  AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
  ORDER BY ITEM_ID,RN
  ) M ;