dorachan2010
dorachan2010

Reputation: 1091

REGEXP_CONTAINS not recognized

Happy new years, stackoverflow!

I am trying to use some regex functions in bigquery but some of them return error as if I have the name wrong.

SELECT REGEXP_CONTAINS(path, r'^abc$') FROM [tablename]

Query Failed
Error: 2.24 - 2.26: Unrecognized function regexp_contains

Where as if I do a similar regex function, the function text in the editor changes color and the query works.

SELECT REGEXP_EXTRACT(path, r'^abc$') FROM [tablename]

It should work since it's documented in this link.

Does anyone know how to fix this?

Upvotes: 2

Views: 6085

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173210

BigQuery Legacy SQL and Standard SQL support different set of regular expression functions Legacy SQL Regular Expression Functions:
REGEXP_MATCH, REGEXP_EXTRACT and REGEXP_REPLACE

Standard SQL Regular Expression Functions:
REGEXP_CONTAINS, REGEXP_EXTRACT, REGEXP_EXTRACT_ALL and REGEXP_REPLACE

So, in your case just make sure you use proper BigQuery SQL dialect

#standardSQL
SELECT REGEXP_CONTAINS(path, r'^abc$') FROM [tablename]

Upvotes: 4

Related Questions