with a as (
select 1 id,'S12abc' name from dual
union all
select 2 id,'aS13abfc' name from dual
union all
select 3 id,'S15abc' name from dual
union all
select 4 id,'1S14abc' name from dual),b as(
select 'S12' xh from dual
union all
select 'S13' xh from dual
union all
select 'S14' xh from dual
)
select *
from (select a.id, (select b.xh from b where instr(a.name, b.xh) > 0) xh
from a)
where xh is not null
select 1 id,'S12abc' name from dual
union all
select 2 id,'aS13abfc' name from dual
union all
select 3 id,'S15abc' name from dual
union all
select 4 id,'1S14abc' name from dual),b as(
select 'S12' xh from dual
union all
select 'S13' xh from dual
union all
select 'S14' xh from dual
)
select *
from (select a.id, (select b.xh from b where instr(a.name, b.xh) > 0) xh
from a)
where xh is not null
(select 1 id, 'S12abc' name
from dual
union all
select 2 id, 'S13asdf' name
from dual
union all
select 3 id, 'S14asdf' name
from dual
union all
select 4 id, '1S15abc' name from dual),
b as
(select 'S12' xh
from dual
union all
select 'S13' xh
from dual
union all
select 'S15' xh from dual)
select a.id, b.xh from a, b where a.name like '%' || b.xh || '%';