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!
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