mysqlregexselect

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

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.