create table test_split(name varchar2(1000),name2 varchar2(1000));
insert into test_split values('a''''b''''c''''d''''a''''b''''c''''d''''','A''''B''''C''''D''''E''''F''''G''''H''''');
select substr(name,instr(''''''||name,'''''',1,level),instr(name,'''''',1,level)-instr(''''''||name,'''''',1,level)) name1
,substr(name2,instr(''''''||name2,'''''',1,level),instr(name2,'''''',1,level)-instr(''''''||name2,'''''',1,level)) name2
,name
from test_split
start with name is not null
connect by
(length(name) - length(replace(name,'''''',''))) / length('''''') - level >= 0 ;
drop table test_split;
上面出现的问题对于我条问题会有些问题,主要是因为 start with 递归调后面的connect by 条件 【 connect by level 在条件中的用法我不是太明白 】
select substr(name,instr(''''''||name,'''''',1,level),instr(name,'''''',1,level)-instr(''''''||name,'''''',1,level)) name1
,name
,substr(name2,instr(''''''||name2,'''''',1,level),instr(name2,'''''',1,level)-instr(''''''||name2,'''''',1,level)) name2
,name2
,level
,(length(name) - length(replace(name,'''''',''))) / length('''''') - level
from test_split
connect by
name = PRIOR name
AND PRIOR dbms_random.VALUE IS NOT NULL
and (length(name) - length(replace(name,'''''',''))) / length('''''') - level >= 0 ;
上面这个是使用 name = PRIOR name AND PRIOR dbms_random.VALUE IS NOT NULL 进行了更新操作。