Looking for information on specific operators in Bigquery, if they exist, and if not, what other operators perform similar functions

Question

I am looking to understand whether or not the below functions are supported in bigquery. I have tried to use them and they are not recognized. If they are not supported, could you recommend what could be used to replace them?

ILIKE operator - case insensitive version of LIKE operator

IGNORE CASE - way to get around not having ILIKE, bigquery does not seem to support

CONTAINS operator - way to get around using wildcard operators with LIKE

Is the only way to do this with the LOWER() operator?

Thanks for the help!

Answer 1

BigQuery is already case sensitive so ILIKE should work with just LIKE.

For IGNORE, since BigQuery is case sensitive you have to use UPPER or LOWER in combination with LIKE. So, UPPER(column) LIKE '%BLAH%'.

For CONTAINS there is REGEXP_CONTAINS, and there is more info here: https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#regexp_contains