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.

Try OR instead of AND: NOT (LOWER(name) LIKE '%ubereats%' OR LOWER(name) LIKE '%uber%eats%') — fen1x
– fen1x, Commented Jul 25, 2022 at 3:59

Erwin Brandstetter · Accepted Answer · 2022-07-25 14:09:10Z

3

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.

edited Jul 25, 2022 at 14:09

answered Jul 25, 2022 at 6:00

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Thorsten Kettner Over a year ago

Shouldn't it be 'uber.*eats' instead of 'uber.?eats'?

Erwin Brandstetter Over a year ago

@ThorstenKettner: Yes, that's a closer fit to the original in the question. (Though it may be the OP really wants a more restrictive pattern, like illustrated in the fiddle with "uber has seats".)

Collectives™ on Stack Overflow

How to exclude rows containing two words in SQL?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related