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)"

score 10 · Accepted Answer · edited Sep 17 '10 at 07:49

10

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).*";

edited Sep 17 '10 at 07:49

onedaywhen

55,269
12
100
138

answered Sep 17 '10 at 02:17

Ned Batchelder

364,293
75
561
662

While correct, I think you should make it explicit that you're pointing out that the OP *can't* do exactly what he's asking. – Adam Robinson Sep 17 '10 at 02:19
I expanded my answer, and added the REGEX option. – Ned Batchelder Sep 17 '10 at 02:26
[REGEXP documentation](http://dev.mysql.com/doc/refman/5.1/en/regexp.html): less efficient than LIKE. – OMG Ponies Sep 17 '10 at 02:30

OMG Ponies · Answer 2 · 2010-09-17T02:25:51.500

6

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.

edited Sep 17 '10 at 02:25

answered Sep 17 '10 at 02:20

OMG Ponies

325,700
82
523
502

While this is more specific, the other one works in the way I wished. – James T Sep 17 '10 at 02:23

onedaywhen · Answer 3 · 2015-03-18T10:25:55.180

1

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";

edited Mar 18 '15 at 10:25

answered Sep 17 '10 at 07:52

onedaywhen

55,269
12
100
138

Thanks. But, it doesn't seem to work as described. I had to CONCAT(MyParamsTable.name, "%") for it to work. – Dave Eckblad Mar 10 '15 at 20:43

MySQL IN with LIKE

3 Answers3

Linked