17,089
社区成员
发帖
与我相关
我的任务
分享
--写法4:
select name,
substr(','||id||',',instr(','||id||',', ',', 1, level) + 1, instr(','||id||',', ',', 1, level + 1) -( instr(','||id||',', ',', 1, level) + 1) )
from tmp
connect by level <= regexp_count(id,',')+1
order by level
--写法2:
select name,trim(regexp_substr(id,'[^,]+',1,level)) as id
from tmp
connect by name = prior name
and prior dbms_random.value is not null
and level <= length(regexp_replace(id, '[^,]'))+1;
--写法3
select name,
regexp_replace(id,'(\w+)\,(\w+)\,(\w+)',level) id
from tmp
connect by level <= regexp_count(id,',')+1;
--创建临时表
create table tmp as(select '张三' name, '1,2,3' id from dual);
select distinct name,regexp_substr(id,'[^,]+',level) as id
from tmp
connect by level <= regexp_count(id,',')+2
order by id