Pattern match within the IN operator (MySQL)

Question

I would like to match patterns inside an IN expression like this:

... WHERE ... IN ('%alma%','%sajt%')

Is it possible somehow and if not, how could I achieve this some other way?

Answer 1

You would need to either use multiple LIKE statements

(columnName LIKE '%alma%' or columnName LIKE '%sajt%' or columnName LIKE '%etc%')

Or look into Full Text Search: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Answer 2

It's not possible. IN is designed to search in potentially large sets, not to make pattern searches.

You should use multiple LIKE conditions separated by OR operators.

Answer 3

As far as I'm aware this is not allowed. You'd have to use multiple LIKE expressions, as in

SELECT *
  FROM SOME_TABLE
  WHERE SOME_VALUE LIKE '%alma%' OR
        SOME_VALUE LIKE '%sajt%';

Share and enjoy.

Answer 4

Other posters have showed you how to use multiple LIKE clauses combined with OR to get this effect. Here are two other thoughts:

• If you find yourself searching inside text fields it is often an indication that you are storing too much information in those fields. If possible, consider breaking out those values into separate fields and / or tables to make searching and validation easier.

• Plain SQL does not excel at searching inside text fields. If the text is large, you have more than a relatively small number of records, and performance is important for this query, consider adding full text indexing to your database.