CODEWITHSUNDEEP
mysqlsql-like
abeger
abeger

Reputation: 6866

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

I'm currently running a query like this:

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

The large number of OR's bothers me. Is there a way to condense this up with something akin to an IN operator, e.g.:

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

Or is this just wishful thinking?

Upvotes: 11

Views: 5941

Answers (2)

Jordan Running
Jordan Running

Reputation: 106027

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

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

Upvotes: 10

Daniel Harcek
Daniel Harcek

Reputation: 329

You can use RLIKE operator (synonym for REGEXP) as well.

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

There might be some performance penalty due to regex matching, but for simple patterns or small sets it should be not an issue. For more on RLIKE see http://dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp

Upvotes: 14

Related Questions