SQL query with many 'AND NOT CONTAINS' statements-CodePudding

I am trying to exclude timezones that have a substring in them so I only have records likely from the US.

The query works fine (e.g., the first line after the OR will remove local_timezones that include 'Africa/Abidjan'), but there's got to be a better way to write it.

It's too verbose, repetitive, and I suspect it's slower than it could be. Any advice greatly appreciated. (I'm using Snowflake's flavor of SQL but not sure that matters in this case).

NOTE: I'd like to keep a timezone such as America/Los_Angeles, but not America/El_Salvador, so for this reason I don't think wildcards are a good solution.

  SELECT a_col
  FROM a_table
  WHERE 
   (country = 'United States')
  OR 
  ((country is NULL and not contains (local_timezone, 'Africa')
  AND
  country is NULL and not contains (local_timezone, 'Asia')
  AND
  country is NULL and not contains (local_timezone, 'Atlantic')
  AND
  country is NULL and not contains (local_timezone, 'Australia')
  AND
  country is NULL and not contains (local_timezone, 'Etc')
  AND
  country is NULL and not contains (local_timezone, 'Europe')
  AND
  country is NULL and not contains (local_timezone, 'Araguaina')
  etc etc

CodePudding user response：

You can use LIKE ANY:

with data as 
(select null country, 'something Australia maybe' local_timezone)

select *
from data
where country = 'United States'
or (
    country is null 
    and not local_timezone like any ('%Australia%', '