Is there a way to combine IN and LIKE in MySQL?

Question

I am currently running a query like this:

SELECT * FROM email WHERE email_address LIKE ' ajones@ %' OR email_address LIKE ' bsmith@ %' OR email_address LIKE ' cjohnson@ %' 

I am worried about a large number of OR . Is there a way to condense this with something like an IN statement, for example:

 SELECT * FROM email WHERE email_address LIKE (' ajones@ %', ' bsmith@ %', ' cjohnson@ %') 

Or is it just wishful thinking?

Answer 1

Here's what I recommend: Extract the portion of the email address before @ and use this before IN :

 SELECT * FROM `email` WHERE LEFT(`email_address`, LOCATE('@', `email_address`) - 1) IN ('ajones', 'bsmith', 'cjohnson') 

Answer 2

You can use the RLIKE operator (synonym for REGEXP ).

 SELECT * FROM email WHERE email_address RLIKE ' ajones@ | bsmith@ | cjohnson@ ' 

Some performance degradation may occur due to regular expression matching, but this should not be a problem for simple patterns or small sets. For more information about RLIKE, see http://dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp