欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

oracle 一行变多行

程序员文章站 2022-04-24 14:52:53
...

1、正则表达式 SELECT distinct T.MY_ID, REGEXP_SUBSTR(T.MY_VALUE, '[^,]', 1, LEVEL) --返回第level次匹配的结果 FROM MY_CHANGE T CONNECT BY LEVEL = REGEXP_COUNT(T.MY_VALUE, ',') 1 --循环次数 ORDER BY T.MY_ID; 2、用connct by rownum 和substr se

1、正则表达式

SELECT distinct T.MY_ID, REGEXP_SUBSTR(T.MY_VALUE, '[^,]', 1, LEVEL) --返回第level次匹配的结果
FROM MY_CHANGE T
CONNECT BY LEVEL ORDER BY T.MY_ID;



2、用connct by rownum 和substr


select my_id, substr(replace(my_value,',',''),tb2.rn,1)sub from
MY_CHANGE ,(select rownum rn from dual connect by rownum where substr(regexp_replace(my_value,',',''),tb2.rn,1) is not null
ORDER BY MY_ID;