#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

'<img[^>]*src="http://www'

Note that the double quotes do not need to be escaped, and the .* at the beginning is implied. By carefully using greedy quantifiers and negated character classes, you can effectively select the desired results from your MySQL table without triggering the "repetition-operator operand invalid" error.