#1139 - Got error 'repetition-operator operand invalid' from regexp

Question

I'm having trouble using a regular expression to select some results from my MySQL table.

I'm using this query

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

And it says

#1139 - Got error 'repetition-operator operand invalid' from regexp

I tested the regex with Notepad++ and it works, why MySQL is giving me this error and how can i fix it?

Solution

According to the MySQL manual

MySQL uses Henry Spencer's implementation of regular expressions, which is aimed at conformance with POSIX 1003.2

POSIX regexes don't support using the question mark ? as a non-greedy (lazy) modifier to the star and plus quantifiers like PCRE (Perl Compatible Regular Expressions). This means you can't use +? and *?

It looks like you'll just have to use the greedy version, which should still work. To avoid the matching of things like <img style="/*some style*/" src="a.png"> <script src="www.example.com/js/abc.js">, you can use a negated character class:

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

Note: The " doesn't have to escaped and the .* at the beginning is implied.