Docs/Error messages/Cannot parse regular expression
Error messagesData values

Cannot parse regular expression: X

BigQuery uses the RE2 engine for regular expressions. RE2 has no lookahead, lookbehind or backreferences and rejects a few other patterns. The message names the part it could not read. A dry run does not catch it.

Cannot parse regular expression: invalid perl operator: (?=

What BigQuery means

REGEXP_CONTAINS, REGEXP_EXTRACT, REGEXP_EXTRACT_ALL, REGEXP_REPLACE and REGEXP_INSTR use the RE2 library. RE2 reads the pattern before it matches anything, and it refuses syntax that can make matching slow. The text after the colon says what it could not read.

The error comes when the query runs. A dry run of SELECT REGEXP_CONTAINS('foobar', r'foo(?=bar)') passes, and the real run fails. A pattern that is never evaluated does not fail, for example one behind WHERE FALSE, or in a query over zero rows. A NULL input never evaluates the pattern either: REGEXP_CONTAINS(NULL, r'foo(?=bar)') returns NULL.

The smallest query that reproduces it

SELECT REGEXP_CONTAINS('foobar', r'foo(?=bar)')
Cannot parse regular expression: invalid perl operator: (?=

A regular expression has to be written in RE2 syntax. For this one, match the text and drop the lookahead:

SELECT REGEXP_CONTAINS('foobar', r'foobar')

The message variants

Each pattern was run with REGEXP_CONTAINS. REGEXP_EXTRACT, REGEXP_REPLACE, REGEXP_EXTRACT_ALL and REGEXP_INSTR gave the same text for the patterns tried. The message does not include the position, only the part of the pattern.

PatternMessage after Cannot parse regular expression:
foo(?=bar)invalid perl operator: (?=
foo(?!baz)invalid perl operator: (?!
(?<=foo)barinvalid perl operator: (?<=
(?<!foo)barinvalid perl operator: (?<!
(?P=x)invalid perl operator: (?P
(?>a)invalid perl operator: (?>
(?z)ainvalid perl operator: (?z
(ab)\1invalid escape sequence: \\1
\y, \i, \Zinvalid escape sequence: \\y, \\i, \\Z
(amissing ): (a
a)unexpected ): a)
[amissing ]: [a
*ano argument for repetition operator: *
a**bad repetition operator: **
a++, a*+bad repetition operator: ++, bad repetition operator: *+
a{2,1}invalid repetition size: {2,1}
a{1001}invalid repetition size: {1001}
[z-a]invalid character class range: z-a
\p{Foo}invalid character class range: \\p{Foo}

The backslash is doubled in the message. It is one backslash in your pattern.

Rewrite for RE2

RE2 supports non-capturing groups (?:...), the flags (?i), named groups (?<name>...) and (?P<name>...), \b, \d, \z, POSIX classes such as [[:alpha:]] and Unicode classes such as \pL. These ran:

SELECT REGEXP_CONTAINS('a1', r'[[:alpha:]][[:digit:]]') AS posix,
       REGEXP_CONTAINS('abab', r'(?P<x>ab)') AS named,
       REGEXP_CONTAINS('é', r'\pL') AS unicode_letter
Pattern RE2 refusesRewriteResult on foobar or the stated text
lookahead foo(?=bar) to testREGEXP_CONTAINS(s, r'foobar')true
lookahead to get what comes afterREGEXP_EXTRACT(s, r'foo(bar)')bar
lookbehind (?<=foo)barREGEXP_EXTRACT(s, r'foo(bar)')bar
lookahead to get what comes beforeREGEXP_EXTRACT(s, r'(foo)bar')foo
negative lookahead ^foo(?!baz)REGEXP_CONTAINS(s, r'^foo') AND NOT REGEXP_CONTAINS(s, r'^foobaz')true for foobar, false for foobaz and bar
backreference ^(ab)\1$REGEXP_CONTAINS(s, r'^(?:ab){2}$')true for abab, false for abac
a backreference to any text, ^(.+)\1$compare the halves: MOD(LENGTH(s), 2) = 0 AND LEFT(s, DIV(LENGTH(s), 2)) = SUBSTR(s, DIV(LENGTH(s), 2) + 1)true for abab, false for abcab and abcd
\Z\ztrue at the end of the text

To replace the text after a prefix, capture it and use the group in the replacement:

SELECT REGEXP_REPLACE('foobar', r'foo(bar)', r'foo-\1')

This returns foo-bar. The \1 in the replacement is fine. Only a \1 in the pattern is refused.

Text that is not a pattern

A pattern built from user text can contain (, +, . or other special characters. REGEXP_CONTAINS('xa(by', 'a(b') fails with missing ): a(b, because the ( starts a group. Two ways out:

SELECT STRPOS('xa(by', 'a(b') > 0

returns true with no pattern at all. Or escape the text first:

SELECT REGEXP_CONTAINS('xa(by', REGEXP_REPLACE('a(b', r'([\\.+*?()|\[\]{}^$])', r'\\\1'))

The escaped pattern is a\(b, and the query returns true.

When the pattern comes from a column

The pattern is checked for each row it is used on, so one bad row fails the whole query:

WITH t AS (SELECT 'abc' AS s, '(a' AS p UNION ALL SELECT 'x', 'x')
SELECT s, REGEXP_CONTAINS(s, p) FROM t
Cannot parse regular expression: missing ): (a

The message does not say which row. To find the rows with a bad pattern, test each pattern on an empty string with the SAFE. prefix. A valid pattern returns true or false, and a bad one returns NULL:

WITH t AS (
  SELECT 'a' AS id, '^a' AS p UNION ALL SELECT 'b', '(b' UNION ALL
  SELECT 'c', NULL UNION ALL SELECT 'd', '(?=d)' UNION ALL SELECT 'e', 'e+')
SELECT id, p FROM t
WHERE p IS NOT NULL AND SAFE.REGEXP_CONTAINS('', p) IS NULL

This returns b and d. Fix those patterns, or leave them out. SAFE.REGEXP_CONTAINS(s, p) also runs without an error, but it returns NULL for the bad rows, so the bad pattern turns into “no match” and nothing says so. Use it to find the rows, then repair them.

Different errors that look similar

MessageCause
Regular expressions passed into extraction functions must not have more than 1 capturing groupREGEXP_EXTRACT('abc', r'(a)(b)'). The pattern is valid. Use one group, or (?:...) for the other
Syntax error: Illegal escape sequence: \d at [1:30]'\d' in a normal string. The string is read before the regex is. Write r'\d' or '\\d'
Syntax error: Unclosed raw string literal at [1:29]r'\'. A raw string cannot end in a single backslash

The '\\d' form is valid and matches a digit. It is the same pattern as r'\d'.

In Querylab.io

The editor does not check regular expression syntax, so a lookahead is not marked as you type. BigQuery accepts the query in a dry run, and the error appears when the query runs. The error bar links to this page. There is no fix button, because the rewrite depends on what the pattern is for.

The editor does mark the string escapes in the last table, because those are errors in the SQL itself: Illegal escape sequence: \d and Unclosed raw string literal.

See Strings and collation.