mysql - #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?

Answer 1

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.