'LIKE ('%this%' OR '%that%') and something=else' not working

Question

I have a select query where I am trying to search strings for multiple patterns

LIKE ('%this%' or '%that%' ) and something=else

Returns zero results

However

LIKE '%this%' and something=else

returns results and

LIKE '%that%' and something=else

returns result

Is it possible to get all my results into one query? If a string matches both, how will it handle that?

If you had proper error checking you'd have gotten notified of the syntax error in the first query. At bare minimum you should have something like `mysql_query(...) or die(mysql_error());` — Marc B, Jun 29 '11 at 21:09
Great answer as tutorial in related thread: http://stackoverflow.com/a/4808757/2906290 — Raúl Moreno, Nov 06 '14 at 19:03

Bohemian · Accepted Answer · 2018-01-24T19:43:51.367

97

It would be nice if you could, but you can't use that syntax in SQL.

Try this:

(column1 LIKE '%this%' OR column1 LIKE '%that%') AND something = else

Note the use of brackets! You need them around the OR expression.
Without brackets, it will be parsed as A OR (B AND C),which won't give you the results you expect.

edited Jan 24 '18 at 19:43

answered Jun 29 '11 at 20:39

Bohemian

412,405
93
575
722

+1 Beat me by 57 seconds. Another option to consider is Full Text Search (FTS) – OMG Ponies Jun 29 '11 at 20:40
3

Thanks! The mistake seems so obvious once you know what it was :) – Kevin Ohashi Jun 29 '11 at 20:45

score 34 · Answer 2 · edited Sep 28 '20 at 08:41

34

Instead of using LIKE, use REGEXP. For example:

SELECT * WHERE value REGEXP 'THIS|THAT'

mysql> SELECT 'pi' REGEXP 'pi|apa';                     -> 1
mysql> SELECT 'axe' REGEXP 'pi|apa';                    -> 0
mysql> SELECT 'apa' REGEXP 'pi|apa';                    -> 1
mysql> SELECT 'apa' REGEXP '^(pi|apa)$';                -> 1
mysql> SELECT 'pi' REGEXP '^(pi|apa)$';                 -> 1
mysql> SELECT 'pix' REGEXP '^(pi|apa)$';                -> 0

Refer: http://dev.mysql.com/doc/refman/5.1/en/regexp.html

edited Sep 28 '20 at 08:41

ΔO 'delta zero'

3,506
1
19
31

answered Jan 22 '14 at 21:02

Nijat Asad

441
4
4

Much more clean and objective than using using multiple LIKES, indeed. – ivanleoncz Feb 01 '17 at 00:17
4

A more readable example: `SELECT id FROM mytable WHERE description REGEXP "dogs|cats"` – Avatar Nov 04 '17 at 08:55

score 5 · Answer 3 · answered Jun 29 '11 at 20:40

5

Try something like:

WHERE (column LIKE '%this%' OR column LIKE '%that%') AND something = else

answered Jun 29 '11 at 20:40

Dirk

3,030
1
34
40

score 3 · Answer 4 · answered Jun 29 '11 at 20:41

3

Break out the LIKE clauses into 2 separate statements, i.e.:

(fieldname1 LIKE '%this%' or fieldname1 LIKE '%that%' ) and something=else

answered Jun 29 '11 at 20:41

Ryan

26,884
9
56
83

score 2 · Answer 5 · answered Jun 29 '11 at 20:40

2

Do you have something against splitting it up?

...FROM <blah> 
   WHERE 
     (fieldA LIKE '%THIS%' OR fieldA LIKE '%THAT%') 
     AND something = else

answered Jun 29 '11 at 20:40

josh.trow

4,861
20
31

score 1 · Answer 6 · answered Jun 29 '11 at 20:40

1

Have you tried:

(column LIKE '%this%' and something=else) or (column LIKE '%that%' and something=else)

answered Jun 29 '11 at 20:40

BumbleB2na

10,723
6
28
30

1

While this will work (and may lead to the exact same plan after optimizations), the non-distributed form is generally preferred -- at least for human consumption. – Jun 29 '11 at 20:44

score 0 · Answer 7 · answered Jan 10 '17 at 13:43

I know it's a bit old question but still people try to find efficient solution so instead you should use FULLTEXT index (it's available from MySQL 5.6.4).

Query on table with +35mil records by triple like in where block took ~2.5s but after adding index on these fields and using BOOLEAN MODE inside match ... against ... it took only 0.05s.

'LIKE ('%this%' OR '%that%') and something=else' not working

7 Answers7

Linked

Related