How to implement “how” in BigQuery?

Question

I am trying to run a simple query creating a% type constraint in BigQuery , but LIKE is not in their syntax, as it can be implemented

Answer 1

You can use the REGEXP_MATCH function (see the request link page ): REGEXP_MATCH ('str', 'reg_exp')

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

Answer 2

LIKE is officially supported by the BigQuery Standard - https://cloud.google.com/bigquery/sql-reference/functions-and-operators#comparison-operators

And I think it also works in Legacy SQL!

Answer 3

REGEXP_MATCH returns true if str matches the regular expression. To match strings without regular expressions, use CONTAINS instead of REGEXP_MATCH.

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

Answer 4

REGEXP_MATCH is great if you know how to use it, but for those who are not sure that regular characters like ".", "$" Or "?" Will not be used. in the search bar, you can use LEFT('str', numeric_expr) or RIGHT('str', numeric_expr) . those. if you have a list of names and you want to return all those that LIKE 's%' you should use:

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

In addition, if you want to say where the values ​​of the LIKE column are foreign, you can change them to 2 for LENGTH(column_with_lookup_strings) and ='sa' for =column_with_lookup_strings , leaving it 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