PostgreSQL NOT IN Multiple Wildcards

Question

Any idea why the following works:

SELECT ID, Name FROM dbo.Survey SURV
WHERE (SURV.Title ILIKE ANY (ARRAY['%Empl%', '%Cont%', '%Staff%']))

But this does not:

SELECT ID, Name FROM dbo.Survey SURV
WHERE (SURV.Title NOT ILIKE ANY (ARRAY['%Empl%', '%Cont%', '%Staff%']))

I am not receiving an error, however, the first query appears to return all proper results, while the second query does not appear to be removing any records from my result set.

Please note: SURV.Title does not have any NULL values in the column.

Mureinik · Accepted Answer · 2018-10-16 16:45:02Z

7

You have a mistake in your boolean logic. Consider, e.g., the string abcEmplxyz. It is ILIKE '%Empl%', but is not ILIKE '%Cont%', so it would be returned. When you negate a boolean condition like that, you need to substitute the any with all:

SELECT ID, Name FROM dbo.Survey SURV
WHERE (SURV.Title NOT ILIKE ALL (ARRAY['%Empl%', '%Cont%', '%Staff%']))
-- Here --------------------^

answered Oct 16, 2018 at 16:45

Mureinik

316k54 gold badges400 silver badges405 bronze badges

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

2 Comments

a1ex07 Over a year ago

or WHERE NOT (SURV.Title ILIKE ANY (ARRAY['%Empl%', '%Cont%', '%Staff%']))

ByRequest Over a year ago

Thanks - this does what I need it to do.

Gordon Linoff · Accepted Answer · 2018-10-16 17:38:20Z

2

Why not just use a regular expression?

WHERE NOT LOWER(SURV.Title) ~ 'empl|cont|staff'

answered Oct 16, 2018 at 17:38

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

PostgreSQL NOT IN Multiple Wildcards

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related