MySQL IN with LIKE

Question

How to use an IN table with similar? So that I can use% in them? 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 this gave the error "The operand must contain 1 column (s)"

Answer 1

You can use several 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 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).*";

Answer 2

There is no combination of LIKE and IN clauses. This is 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%')

An alternative to text search 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.

Answer 3

Put the values ​​in table ( MyParamsTable) and use LIKEin state JOIN, for example. sort of:

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