1.方法一 (sql方法)
with temp0 as
(select LEVEL lv from dual CONNECT BY LEVEL <= 100)
select fd_objectid,
substr(t.vals,
instr(t.vals, ',', 1, tv.lv) + 1,
instr(t.vals, ',', 1, tv.lv + 1) -
(instr(t.vals, ',', 1, tv.lv) + 1)) AS name
from (select fd_objectid,
',' || check_type || ',' AS vals,
length(check_type || ',') -
nvl(length(REPLACE(check_type, ',')), 0) AS cnt
from DAN_CHECK_INFO
where is_del = 0) t
join temp0 tv
on tv.lv <= t.cnt
order by 1
(select LEVEL lv from dual CONNECT BY LEVEL <= 100)
select fd_objectid,
substr(t.vals,
instr(t.vals, ',', 1, tv.lv) + 1,
instr(t.vals, ',', 1, tv.lv + 1) -
(instr(t.vals, ',', 1, tv.lv) + 1)) AS name
from (select fd_objectid,
',' || check_type || ',' AS vals,
length(check_type || ',') -
nvl(length(REPLACE(check_type, ',')), 0) AS cnt
from DAN_CHECK_INFO
where is_del = 0) t
join temp0 tv
on tv.lv <= t.cnt
order by 1
2.方法二(正则表达式方法)
with a as (select '/ABC/AA/AD/ABD/JI/CC/ALSKD/ALDKDJ' id from dual)
select regexp_substr(id,'[^/]+',1,rownum) id from a
connect by rownum<=length(regexp_replace(id,'[^/]+'))
select regexp_substr(id,'[^/]+',1,rownum) id from a
connect by rownum<=length(regexp_replace(id,'[^/]+'))