How to implement "like" in BigQuery?

Question

I am trying to run a simple query making a restriction of like % in BigQuery, but LIKE is not in their syntax, so how can it be implemented?

Answer 1

You can use the REGEXP_MATCH function (see the query reference page): REGEXP_MATCH('str', 'reg_exp')

Instead of using the % syntax used by LIKE, you should use regular expressions (detailed syntax definition here)

Answer 2

REGEXP_MATCH is great if you know how to use it, but for those who aren't sure there won't be any commonly used special characters such as '.','$' or '?' in the lookup string, you can use LEFT('str', numeric_expr) or RIGHT('str', numeric_expr). ie if you had a list of names and wanted to return all those that are LIKE 'sa%' you'd use:

select name from list where LEFT(name,2)='sa'; (with 2 being the length of 'sa')

Additionally, if you wanted to say where one column's values are LIKE another's, you could swap out the 2 for LENGTH(column_with_lookup_strings) and ='sa' for =column_with_lookup_strings, leaving it looking something like this:

select name from list where LEFT(name,LENGTH(column_with_lookup_strings))= column_with_lookup_strings;

https://cloud.google.com/bigquery/query-reference

Answer 3

LIKE is officially supported in BigQuery Standard SQL - https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#comparison_operators

And I think it also works in Legacy SQL!

Answer 4

REGEXP_MATCH returns true if str matches the regular expression. For string matching without regular expressions, use CONTAINS instead of REGEXP_MATCH.

https://developers.google.com/bigquery/docs/query-reference#stringfunctions