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)?
Advertisement
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