20

I always thought that you could use OR in a LIKE statment to query things in MySQL. So, if I wanted to compare multiple fields in a row to 1 keyword or term:

SELECT * FROM MyTable WHERE Column1 OR Column2 LIKE '%keyword%';

and if I had an array of words to compare:

SELECT * FROM MyTable WHERE Column1 OR Column2 LIKE '%keyword1%' 
AND Column1 OR Column2 LIKE '%keyword2%';

I don't believe that syntax is correct, however. Is there an efficient method of writing this aside from something like:

SELECT * FROM MyTable WHERE Column1 LIKE '%keyword1%' OR Column2 LIKE 
'%keyword1%' AND Column1 LIKE '%keyword2%' OR Column2 LIKE '%keyword2%';

Am I going about this correctly?

Aldwoni
  • 1,168
  • 10
  • 24
RCNeil
  • 8,581
  • 12
  • 43
  • 61
  • I'm just wondering about your "array of words" algorithm anyway... Are you sure you want to retrieve results if a column is like a word AND if a column is like another? It seems pretty much inefficient to me – Sebas Jun 19 '12 at 18:25
  • The keyword(s) are coming from a search field, so users will want to search a database based on many fields. If they use 2 words in their search term, it still makes sense to search both columns for this term. I don't want to exclude a field because the possibility that they contain multiple related words is there. – RCNeil Jun 19 '12 at 18:38

3 Answers3

34

Use this::

SELECT * FROM MyTable WHERE (Column1 LIKE '%keyword1%' OR Column2 LIKE 
'%keyword1%') AND (Column1 LIKE '%keyword2%' OR Column2 LIKE '%keyword2%');
Sashi Kant
  • 13,277
  • 9
  • 44
  • 71
  • ah. I take it the parenthesis are necessary? That would explain why it wasn't working before.... – RCNeil Jun 19 '12 at 18:21
24

The closest to the syntax you are desiring is:

SELECT * FROM MyTable
WHERE (CONCAT(Column1, Column2) LIKE '%keyword1%')
AND (CONCAT(Column1, Column2) LIKE '%keyword2%')

Note: that the "%" at the start of your search string precludes the use of indexes. If there are any large number of records to search, it would be best to rethink the implementation.

If you cannot guarantee that each column is not NULL, then use CONCAT_WS instead:

SELECT * FROM MyTable
WHERE (CONCAT_WS("-", Column1, Column2) LIKE '%keyword1%')
AND (CONCAT_WS("-", Column1, Column2) LIKE '%keyword2%')

This CONCAT_WS solution also has the possible benefit of assuring that matches of your "keyword" where in only in Column1 OR Column2, if you select a separator character that is never present in your keywords.

Mazrick
  • 1,149
  • 8
  • 10
  • 1
    If you add two character columns together, they will be cast as numbers and added. Most likely, the result will be zero, and your WHERE clause will never evaluate to true. To concatenate columns in MySQL, use CONCAT. – Marcus Adams Jun 19 '12 at 19:32
  • 3
    I'd recommend CONCAT_WS unless you can be assured that Column1 and Column2 will never be null. Otherwise they will return false. It may still match one of the columns. – earl3s Jan 22 '14 at 18:38
  • If column1 has value "xxxkey" and column2 has value "word1xx", your query will treat it as true, which is WRONG. – Sashi Kant Mar 21 '19 at 04:39
0

You can compare a field to multiple strings at once in MySQL with REGEXP_LIKE()

SELECT *
FROM MyTable
WHERE REGEXP_LIKE(Column1, 'keyword1|keyword2') OR REGEXP_LIKE(Column2, 'keyword1|keyword2')
Stevoisiak
  • 23,794
  • 27
  • 122
  • 225