SQL where in with lists in Oracle PL/SQL?

Question

I have the following "table1" :

NAME     JOB    

Mary     pilot, astronaut, cook
John     astronaut, biker
Michael  rider
Rita     teacher, doctor

I want to select all people who are an astronaut OR a doctor. It should return Mary, John and Rita.

I currently have:

select name from table1 
where 'astronaut,doctor' in (select regexp_substr(table1.job,'[^'',]+', 1, level) 
     from dual
     connect by regexp_substr(table1.job, '[^'',]+', 1, level) is not null)

However, I don't want to compare the whole string on the left-hand side, instead I want to iterate through it.

Note: I'm getting the left-hand argument as an input argument, so it must be parsed inside the sql.

Answer 1

You should fix your data format. You are trying to store lists in a column, and that is a bad idea. You should be using a junction table rather than delimited values.

Sometimes, we are stuck with other people's bad design decisions. Oracle does have powerful regular expression operators. This does allow:

select name, job
from table1
where regexp_like(job, replace('astronaut,doctor', ',', '|');