Why Does MySQL REGEXP Throw \'#1139 Error: Invalid Repetition Operator Operand\' and How Can I Fix It?

#1139 Error: Invalid Repetition Operator Operand in Regular Expression

When querying a MySQL table with a regular expression (regex) to select specific results, users occasionally encounter the error "#1139 - Got error 'repetition-operator operand invalid' from regexp." This error indicates that the provided regex contains an invalid repetition operator operand.

Cause:

The error arises because MySQL uses Henry Spencer's POSIX-compliant implementation of regular expressions. This implementation doesn't support non-greedy quantifiers like "?," used in PCRE (Perl Compatible Regular Expressions). Therefore, using "?," after "*" or " " in a regex will trigger an error.

Solution:

To resolve this issue, replace the non-greedy quantifier "?" with the greedy quantifier "*", which will still perform the necessary matching:

SELECT text
FROM `articles`
WHERE content REGEXP '.*<img[^>]*src="http://www'
ORDER BY date DESC

Additionally, to avoid matching strings such as or