with tab1 as (
select 'aabbccddeeff' src from dual union all
select 'aabbccddeeffgggggggggggggg' from dual
),
tab2 as (
select 'bb' str from dual union all
select 'ee' from dual union all
select 'ff' from dual
)
, tab3 as (
select src, result_ from tab1 t1, tab2 t2
where 1 = 1
and instr(t1.src, t2.str) > 0
model
partition by (src)
dimension by (row_number() over(partition by t1.src order by t1.src) rn)
measures (str, src result_)
rules(
result_[rn] = regexp_replace(nvl(result_[cv() - 1], result_[cv()]), str[cv()])
)
)
, tab4 as (
select src, row_number() over(partition by t1.src order by length(result_)) rn, result_ from tab3 t1
)
select * from tab4 t1
where t1.rn = 1
;