CODEWITHSUNDEEP
google-bigquery
John
John

Reputation: 1039

How to use bigquery to test if string in list of strings with wildcards

I would like to write something equivalent to the below, but it appears not to work. Is this possible in BigQuery?

select *
from table
where lower(field1) in ("%foo%", "%bar%", "%baz%")

I also need this functionality inside a case when clause

case when (lower(field1) in ("%foo%", "%bar%")) then "string1"
else "string2" end as NewField

Upvotes: 1

Views: 2336

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172984

Use below approach

select *
from table
where regexp_contains(lower(field1), r'foo|bar|baz')

Upvotes: 3

Related Questions