CODEWITHSUNDEEP
mysqlsql-like
RCNeil
RCNeil

Reputation: 8759

Using OR in LIKE Query in MySQL to compare multiple fields

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?

Upvotes: 20

Views: 63914

Answers (3)

Stevoisiak
Stevoisiak

Reputation: 26752

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

Upvotes: 0

Mazrick
Mazrick

Reputation: 1159

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.

Upvotes: 24

Sashi Kant
Sashi Kant

Reputation: 13465

Use this::

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

Upvotes: 34

Related Questions