How to exclude rows containing two words in SQL?

Question

I'm trying to exclude rows that contain two words. In more details, I want to include rows that contain uber, but don't include rows that contain uber eats.

I'm using this condition:

WHERE LOWER(name) LIKE '%uber%'  
AND NOT (LOWER(name) LIKE '%ubereats%' AND LOWER(name) LIKE '%uber%eats%')

But I still get some unwanted rows like the following:

UBER   EATS
UBER   *EATS
UBER* EATS

In a query, I need to exclude any of uber eats related ones. In a separate query, I want only those that are related to uber eats.

Answer 1

Basically, you need OR instead of AND. Plus parentheses due to operator precedence (AND binds stronger than OR):

WHERE  LOWER(name) LIKE '%uber%'  
AND    NOT (LOWER(name) LIKE '%ubereats%' OR
            LOWER(name) LIKE '%uber%eats%')

Or keep the AND and switch to NOT LIKE:

WHERE  LOWER(name) LIKE '%uber%'  
AND    LOWER(name) NOT LIKE '%ubereats%'
AND    LOWER(name) NOT LIKE '%uber%eats%';

But since % in LIKE patterns stands for 0 or more characters, LOWER(name) LIKE '%ubereats%' is completely redundant. So:

WHERE  LOWER(name) LIKE '%uber%'  
AND    LOWER(name) NOT LIKE '%uber%eats%'

Or simplify altogether with regular expressions:

WHERE name ~* 'uber'  
AND   name !~* 'uber.*eats'

db<>fiddle here

See:

• String matching in PostgreSQL
• Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

I am not sure your original expressions capture your objective precisely, though.