17,086
社区成员
发帖
与我相关
我的任务
分享
with tab1 as (
select 'beijing' iid, 'CR56,2103,0305;CR56,2104,0306;' txt from dual union all
select 'shanghai' iid, 'CR56,2103,0305;CR56,2104,0306;' txt from dual
),
tab2 as (
select t1.*, regexp_substr(t1.txt, '[^;]+', 1, level) r_txt
from tab1 t1
connect by prior t1.iid = t1.iid
and level <= regexp_count(t1.txt, ';')
and prior dbms_random.value is not null
)
select t1.*,
regexp_substr(t1.r_txt, '[^,]+', 1, 1) r0,
regexp_substr(t1.r_txt, '[^,]+', 1, 2) r1,
regexp_substr(t1.r_txt, '[^,]+', 1, 3) r2
from tab2 t1
;