CODEWITHSUNDEEP
mysqlsql
Cous
Cous

Reputation: 89

Using NOT LIKE on multiple columns

Got a search where I'm right now building my own version of the query filtering that exists in for example Boolean search. I mean that I can do office +sweden that should mean that sweden has to exists. We can't use BOOLEAN search for reasons that are outside of this question.

Say that I have a table called documents that have two columns on it content and notes. When I type, in my search bar, +office +sweden I do something similar to this that works:

SELECT * FROM documents
WHERE (content LIKE '%office%' or notes LIKE '%office%') AND
      (content LIKE '%sweden%' or notes LIKE '%sweden%')

This works perfectly so far. This will only give me documents where office AND sweden is somewhere either in content or notes.

Now I want to do the same thing but with a minus. For example if I search for office -sweden I want to get all the documents that contains office and does not contain sweden in either contents or notes (yes, with the wildcard as well).

I have tried multiple ways without getting it to work properly. I've tried with NOT LIKE for example without getting it to work:

SELECT * FROM documents
WHERE (content LIKE '%office%' or notes LIKE '%office%') AND
      (content NOT LIKE '%sweden%' or notes NOT LIKE '%sweden%')

Feels like the or is messing it up with this one. I've also tried using AND NOT

SELECT * FROM documents
WHERE (content LIKE '%office%' or notes LIKE '%office%') AND NOT
      (content LIKE '%sweden%' or notes LIKE '%sweden%')

I had high hopes about the AND NOT but this doesn't work as expected either.

Hope it's clear what I want to do. Get all the documents that contain office but does not contain sweden using LIKE.

Solution

Turns out that there were nothing wrong with the queries itself. I had more fields in the query that could be null. I omitted these fields in the question to try and make it easier to understand. If you have fields that can contain null make sure to handle that like Gordon says below.

I handled null fields with the IFNULL operator like so:

SELECT * FROM documents
WHERE (content LIKE '%office%' or notes LIKE '%office%' or nullfield LIKE '%office%') AND
      NOT (IFNULL(content, '') LIKE '%sweden%' or IFNULL(notes, '') LIKE '%sweden%' or IFNULL(nullfield, '') LIKE '%sweden%')

Upvotes: 1

Views: 1168

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

Just use not like:

SELECT *
FROM documents
WHERE (content LIKE '%office%' or notes LIKE '%office%') AND
      (content NOT LIKE '%sweden%' and notes NOT LIKE '%sweden%');

Or if you prefer:

SELECT *
FROM documents
WHERE (content LIKE '%office%' or notes LIKE '%office%') AND
      NOT (content LIKE '%sweden%' or notes LIKE '%sweden%');

One caveat: NOT LIKE ignores NULL values just as LIKE does. So if the values can be NULL, you need to take that into account.

Upvotes: 2

Related Questions