KNOWLEDGE BASE

Escaping Backslash Not Working When Using Parameter To Set Regular Expressions In Custom SQL (Google BigQuery)


Published: 31 Aug 2021
Last Modified Date: 31 Aug 2021

Issue

Escaping a backslash in a parameter does not work as expected when regular expression is used in custom SQL in Google BigQuery.

For example:

-Sample data [Table]:
Column
aa
a.

-Custom SQL:
select * from Table where regexp_contains(Column,r<parameter>)

-Parameter:
a\.

-Expected result:
Column
a.

-Actual result:
No record returned

*If set parameter as 'a.' without escaping backslash, then the result will be 
Column
aa
a.


 

Environment

  • Tableau Desktop
  • Google BigQuery

Resolution

Change the Custom SQL
---------------------
select * from Table where regexp_contains(Column,r<parameter>)
---------------------
To
---------------------
select * from Table where regexp_contains(Column,<parameter>)
---------------------
*Remove 'r'
 

Additional Information

This is due to the regular expression syntax used In Google BigQuery as shown below:
REGEXP_CONTAINS(sample, r"@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+")
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#regexp_contains

Please note there is a 'r' in front of the regular expression.
However, when using a parameter to set regular expression, escaping backslash is not working as expected.
If remove the 'r', then escaping backslash is working as expected.
Did this article resolve the issue?