1

How do you combine NOT IN and LIKE?

Let's assume we have a table that contains a column of names (something like 'blue cheese', 'gouda cheese' and so on) and I want to select all the names that doesn't contain 'cheese', 'milk', 'meat'.

As far as I understand to look for something that is not in an array of strings you use NOT IN and the pass the strings 

SELECT names FROM some_table NOT IN('cheese','milk','meat');

but how do I pass

LIKE '%cheese%'

to it?

Beryllium
  • 12,808
  • 10
  • 56
  • 86
boo_boo_bear
  • 189
  • 1
  • 4
  • 14

1 Answers1

5

The construct LIKE ANY (ARRAY[...]) appears to meet your needs;

craig=> SELECT a FROM (
           VALUES ('cheesy'), ('imilk'), ('donut'), ('pie'), ('avocado'), ('meaty')
        ) x(a) 
        WHERE NOT a LIKE ANY (ARRAY['%cheese%','%milk%','%meat%']);

    a    
---------
 cheesy
 donut
 pie
 avocado
(4 rows)

You need the wildcard characters if you want to use LIKE this way. If you really just want equality, you can use:

NOT = ANY (...)
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • 1
    @boo_boo_bear: Or `WHERE a !~~ ALL (...)` / `WHERE a <> ALL (...)` if there are no `NULL` values involved. – Erwin Brandstetter Jun 10 '13 at 23:11
  • Why `where "ID" not like any ( array['RC11909%','RC11917%','RC14755%','RC14990%'])` doesn't work but `where not "ID" like any ( array['RC11909%','RC11917%','RC14755%','RC14990%'])` works as expected? – Kamil Kacprzak May 30 '23 at 10:29