check if all elements in hive array contain a string pattern

[#1]

Hello hortonworks family!

I have two columns in a hive table that look something like this:

code	codeset
AB	AB123,MU124
LM	LM123,LM234

I need to verify that all elements in codeset column contain the value in code column so in the above example the first row would be false and the second row would be true.

Is there a simple way to do this that I am missing? I already read about array_contains but that returns true if just one element matches, I need all elements to contain what's in the code column.

Thanks in advance.

[#2]

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)