'remove rows based on column value in bigquery

I have a table with the following data, I want to remove rows where the country value is ['US','EU','pl','nl','es']

#standardSQL
with table as (
  select 'DE' country, 520 number union all
  select 'NL' country, 520 number union all
  select 'US' country, 530 number union all
  select 'AT' country, 560 number union all
  select 'NL' country, 720 number union all
  select 'CH' country, 5280 number union all
  select 'FR' country, 5250 number union all
  select 'BE' country, 5280 number union all
  select 'IT' country, 4890 number union all
  select 'EU' country, 5005 number union all
  select 'pl' country, 5007 number union all
  select 'nl' country, 5005 number union all
  select 'es' country, 5070 number union all
  select 'ROE' country, 5700 number
)
select * from table
where country not in ['US','EU','pl','nl','es']

I understand that where statement doesn't work like that but this is something I wanted to achieve, can some help here. Thank you in advance!

sqlgoogle-bigquery


Solution 1:[1]

Use below instead

select * from table
where not lower(country) in ('us','eu','pl','nl','es')

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Mikhail Berlyant

Related Questions