Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Where statement with multiple 'Not Like'

I'm trying to write the following statement:

WHERE field LIKE 'Pandora' AND field Not Like 'radio', 'digital', 'internet';

Translation: Select where field is like Pandora and not like radio, digital, or internet.

Is there a way to write this statement without writing Not Like 3 times with ANDs in between?

Thank you

like image 359
ZJAY Avatar asked Oct 25 '13 00:10

ZJAY


2 Answers

If "field" is not just single words, you would need to do something like this:

SELECT * FROM table WHERE field LIKE '%Pandora%' AND field NOT LIKE '%radio%' AND field NOT LIKE '%internet%' and field NOT LIKE '%digital%';
like image 149
Kurt Avatar answered Sep 21 '22 21:09

Kurt


First of all, your query is redundant, in that if field is LIKE 'pandora', then the other conditions will by default return false.

There is no possible way that field can be equal to 'Pandora', 'radio', 'digital', and 'internet'.

As a result, you can simplify your query using the following example:

SELECT *
  FROM example
 WHERE field = 'Pandora';

If the two conditions represent two separate fields, then you can use the REGEXP operator to enforce the DRY principle while still allowing for further pattern matching:

SELECT *
  FROM example
 WHERE field_1 = 'Pandora'
   AND field_2 NOT REGEXP '^(radio|digital|internet)$';
like image 28
Grant Miller Avatar answered Sep 24 '22 21:09

Grant Miller