[FIXED] PHPMyAdmin Match rows with two or more strings

Issue

I have a huge database and inside that database I want to find rows from the column 'title' that doesn't contain the words "GmbH", "GbR", "e.K.", etc.

I tried this:

SELECT * FROM `listings` WHERE `listings`.`title` NOT LIKE '%GmbH' AND `listings`.`email_allow`=0

this works but this shows me all other rows with these other words so how can I write a single query that shows me only the rows that do not contain these words.

These queries were unsuccessful:

   1. SELECT * FROM `listings` WHERE `listings`.`title` NOT LIKE '%GmbH' OR '%GbR' OR '%AG' AND `listings`.`email_allow`=0
   2. SELECT * FROM `listings` WHERE `listings`.`title` NOT LIKE ('%GmbH' OR '%GbR' OR '%AG') AND `listings`.`email_allow`=0
   3. SELECT * FROM `listings` WHERE (`listings`.`title` NOT LIKE '%GmbH' OR '%GbR' OR '%AG') AND `listings`.`email_allow`=0

Solution

You need to repeat the NOT LIKE:

SELECT l.*
FROM `listings` l
WHERE l.title NOT LIKE '%GmbH' AND
      l.title NOT LIKE '%GbR' AND
      l.title NOT LIKE '%AG' AND
      l.email_allow = 0;

You might want to consider a full text index, if you have queries like this. LIKE can be pretty expensive.

You can also do this using regular expressions:

SELECT l.*
FROM `listings` l
WHERE l.title NOT REGEXP '(GmbH|GbR|AG)$' AND
      l.email_allow = 0;

Regular expressions will not necessarily be faster, but it is more concise.

Answered By - Gordon Linoff