oralce下如何用一条语句把一个字符串分成多行

William 发布于 2014/09/23 19:14
阅读 644
收藏 0

如'20140901(3;1),20140904(3),20140909(3)'

转化后的最终结果是

20140901

20140904

20140909

3行记录

不要问我为什么有这样的想法,我也不想

以下是问题补充:

@William:oracle10g (2014/09/24 13:50)
加载中
0
StormFour
StormFour
with v1 as
(
  select ',' || '20140901(3;1),20140904(3),20140909(3)' c from dual
)
select substr(c, instr(c, ',', 1, level) + 1, instr(c, '(', 1, level) - instr(c, ',', 1, level) - 1) rq
from v1 connect by level <= length(c) - length(replace(c, '('))
0
jolphin
jolphin
WITH t AS
(
 SELECT '20140601(3;1),20140604(3),20140609(3)' cc FROM dual UNION ALL
 SELECT '20140701(4;1),20140704(4),20140709(4)' cc FROM dual UNION ALL
 SELECT '20140801(5;1),20140804(5),20140809(5)' cc FROM dual UNION ALL
 SELECT '20140901(6;1),20140904(6),20140909(6)' cc FROM dual
)
SELECT regexp_substr(cc,'(\d+)([0-9();]+)',1,LEVEL,'i',1) FROM t
CONNECT BY LEVEL<=regexp_count(cc,'[,]')+1
AND  PRIOR cc=cc

AND PRIOR dbms_random.value>0;

支持oracle11g以上版本

0
William
William

多谢楼上两位,10g下regexp_count用不了,我改造了下

with a as (select REGEXP_REPLACE('20140901(3;1),20140904(3),20140909(3)', '\([0-9;]*\)') id from dual)
select regexp_substr(id,'[^,]+',1,level) rq 
from a connect by level <= length(regexp_replace(id,'[^,]+'))+1;




返回顶部
顶部