MySQL IN with LIKE

Question

How would I use a IN table with like? So that I could use % in them? By in I mean:

SELECT fields 
  FROM table 
 WHERE age = "50" 
   AND name IN ("tim", "bob", "nancy", "john");

I already tried:

SELECT fields 
  FROM table 
 WHERE age = "50" 
   AND name LIKE ("2010-09-17%", "2010-09-16%")

But it gave the error "Operand should contain 1 column(s)"

Solution 1

You can use a number of LIKE expressions:

SELECT fields 
  FROM table 
  WHERE age = "50" 
        AND (
             name LIKE "2010-09-17%" 
             OR name LIKE "2010-09-16%"
            );

or you can use a regex:

SELECT fields 
  FROM table 
 WHERE age = "50" 
       AND name REGEXP "2010-09-17.*|2010-09-16.*";

or, cleverly

SELECT fields 
  FROM table 
 WHERE age = "50" 
       AND name REGEXP "2010-09-1(6|7).*";

Solution 2

There is no combination of the LIKE and IN clauses. It's either one, or the other, syntax:

SELECT fields
  FROM table
 WHERE age = 50
   AND (   name IN ('tim', 'bob', 'nancy', 'john')
        OR name LIKE '2010-09-17%'
        OR name LIKE '2010-09-16%')

The alternative to consider when searching text is Full Text Search (FTS):

SELECT fields
  FROM table
 WHERE age = 50
   AND MATCH(name) AGAINST('tim bob nancy john')

...but this requires MyISAM tables, and Full Text Indexing.

Solution 3

Put the values in a table (MyParamsTable) and use LIKE in a JOIN condition e.g. something like:

SELECT fields 
  FROM table 
       INNER JOIN MyParamsTable
          ON table.name LIKE CONCAT(MyParamsTable.name, "%")
 WHERE age = "50";