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

William 发布于 2014/09/23 19:14

20140901

20140904

20140909

3行记录

### 以下是问题补充：

@William：oracle10g (2014/09/24 13:50)

0
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
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;

0

```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;```