首页 >数据库 >mysql教程 >oracle 一行变多行

oracle 一行变多行

WBOY
WBOY原创
2016-06-07 15:19:382589浏览

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;

声明:
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn