How to escape a percent sign in BigQuery queries?

We are attempting to select rows from a field in BigQuery that contains only a percent sign “%”.

We have tried the answers to various other questions for escaping like this:

SELECT COUNT(1) FROM Table WHERE field_name LIKE "%"

This gives an invalid escape character error with single or double quotes.

We also tried other variation escape characters shown for other flavors of SQL:

SELECT COUNT(1) FROM Table WHERE field_name LIKE "!%"

This gave us zero results as it didn’t match the rows with percent signs.

We tried these queries in legacy and standard SQL.

How do we escape a percent sign for SELECT/WHERE statements in standard SQL for BigQuery (and/or legacy sql)?

Answer

The simplest fix is below

SELECT COUNT(1) FROM Table WHERE field_name LIKE '%\%%'    

Meantime, another option is to use REGEXP_CONTAINS function (BigQuery Standard SQL)

SELECT COUNT(1) FROM Table WHERE REGEXP_CONTAINS(field_name, r'%')   

For BigQuery Legacy SQL – you should use REGEXP_MATCH instead of REGEXP_CONTAINS