MySQL NOT IN Query problem

Question

I am trying to search a table where the field name does not have a list of strings in it. I use:

SELECT * 
 FROM members  
WHERE name NOT IN ('bob', 'jim', 'leroy');

but it still returns matches containing those words. I have searched high and low for the answer to this. Can anyone help?

Answer 1

name NOT IN ('bob', 'jim', 'leroy') is equivalent to name!='bob' and name!='jim' and name!='leroy'.

Perhaps you want

name not like '%bob%' and name not like '%jim%' and name not like '%leroy%'

instead?

Answer 2

This only NOT matches values that are exactly one of the names. You can try WHERE name NOT LIKE "%bob%" AND NOT LIKE "%jim%" AND NOT LIKE "%leroy%"