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.
| Pattern | Message after Cannot parse regular expression: |
|---|---|
foo(?=bar) | invalid perl operator: (?= |
foo(?!baz) | invalid perl operator: (?! |
(?<=foo)bar | invalid perl operator: (?<= |
(?<!foo)bar | invalid perl operator: (?<! |
(?P=x) | invalid perl operator: (?P |
(?>a) | invalid perl operator: (?> |
(?z)a | invalid perl operator: (?z |
(ab)\1 | invalid escape sequence: \\1 |
\y, \i, \Z | invalid escape sequence: \\y, \\i, \\Z |
(a | missing ): (a |
a) | unexpected ): a) |
[a | missing ]: [a |
*a | no 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 refuses | Rewrite | Result on foobar or the stated text |
|---|---|---|
lookahead foo(?=bar) to test | REGEXP_CONTAINS(s, r'foobar') | true |
| lookahead to get what comes after | REGEXP_EXTRACT(s, r'foo(bar)') | bar |
lookbehind (?<=foo)bar | REGEXP_EXTRACT(s, r'foo(bar)') | bar |
| lookahead to get what comes before | REGEXP_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 | \z | true 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
| Message | Cause |
|---|---|
Regular expressions passed into extraction functions must not have more than 1 capturing group | REGEXP_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.