Why Does My MySQL REGEXP Query Fail with \'repetition-operator operand invalid\'?

Failed Regular Expression: 'repetition-operator operand invalid'

When attempting to execute the following MySQL query:

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

you encounter the error: #1139 - Got error 'repetition-operator operand invalid' from regexp. Despite the regular expression functioning correctly in Notepad , MySQL rejects it.

Understanding the Issue: POSIX vs. PCRE

The MySQL regular expression engine adheres to POSIX 1003.2, which lacks support for the question mark (?) as a non-greedy modifier in quantifiers ( and ). Consequently, you cannot employ ? and ? in your regular expressions.

Resolution: Utilizing a Greedy Quantifier

To resolve this issue, employ the greedy version of the quantifier, which will still suffice for your intended purpose. Nevertheless, to prevent undesired matching of elements like:

<img>

incorporate a negated character class as follows:

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

Remember that the " character does not necessitate escaping and that the .* at the beginning is implicit.

The above is the detailed content of Why Does My MySQL REGEXP Query Fail with \'repetition-operator operand invalid\'?. For more information, please follow other related articles on the PHP Chinese website!