KNOWLEDGE BASE

Unable to use regular expressionREGEXP_MATCH in Google BigQuery


Published: 14 Dec 2016
Last Modified Date: 08 May 2017

Issue

Attempting to use the function REGEXP_MATCH() in a calculated field while connected to Google BigQuery results in the following error message:

An error occurred while communicating with the Google BigQuery data source.
The Google BigQuery service was unable to compile the query.
Function not found: REGEXP_MATCH at [x:xxx]

Environment

  • Tableau Desktop 10.1.1 and earlier versions
  • Google BigQuery

Resolution

Option 1

Upgrade to Tableau Desktop 10.1.4 or a later version. For more information, see Upgrading Tableau Desktop. 

Option 2

Use a data extract. For more information, see Extract Your Data

Option 3

Use the CONTAINS() function instead.
  • This option will only work if the matching pattern is a string literal.
  • If regular expression metacharacters and operators are used, please refer to Option 2.

Cause

This behavior is related to a known issue (ID: 597583) that has been fixed in a later version of Tableau Desktop. Google BigQuery connections were updated to utilize the REGEXP_CONTAINS function when REGEXP_MATCH was called. 
  • REGEXP_MATCH is not an available function in the current Google BigQuery SQL syntax. It was available in previous versions but has been deprecated.
  • The current syntax has replaced it with the REGEXP_CONTAINS function.
  • The legacy syntax when generating queries was used in version 10.1.1 of Tableau Desktop (and earlier).

Additional Information

For more information, see the "Google BigQuery SQL Reference" on the differences between the current standard syntax and the legacy syntax: Migrating to Standard SQL.
Did this article resolve the issue?