CODEWITHSUNDEEP
sqlstringpostgresql
mah65
mah65

Reputation: 588

How to exclude rows containing two words in SQL?

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.

Upvotes: 1

Views: 496

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657932

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:

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

Upvotes: 3

Related Questions