Not very clean - but you can try something like this - with src as (select 'AB' as code, ARRAY('AB123','MU124') as codeset union all select 'LM' as code, ARRAY('LM123','LM234') as codeset) select a.code, CASE WHEN e.c is null THEN False ELSE TRUE END as condition from (select code, size(codeset) c from src) as a left join (select code, count(*) c from (select code, explode(codeset) as val from src) where instr(val, code) > 0 group by code) e on (a.code=e.code and a.c=e.c) ... View more