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

Abohm

This won't work well with any moderately sized tables. – Algo

@Algo I won't use this select more than once, i need this once to see which records in my table have absolute urls for images instead of relative urls and fix them, then i won't need it anymore – Abohm

You can try LIKE '%<img src="http://www%' ... Simpler – Groove

@Groove No. I don't know if i have <img src="http://www or <img style="..." src="http://www", that's why i'm using a regex, i have to check the image source even if the <img tag contains other attributes before the src one – Abohm

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.

Algo

That doesn't work. if i have <img style="/*some style*/" src="a.png"> <script src="http://www.example.com/js/abc.js"> it would return me that row, which is wrong. – Abohm

+1 - Never use .* when a more precise expression can be crafted, i.e. [^>]* – Seline

Answer 2

You can try,

SELECT 
        text 
        , 
     IF (content LIKE '%<img src="http://%', text  , content LIKE '%<img style=%') 
as imageText

FROM    articles ORDER BY date DESC

This will Check first for where content has <img src="http:// if it can't find then it will look for <img style= instead.

Hope it Helps.

Check Fiddle: http://sqlfiddle.com/#!2/6a2f0/13/0

Groove

Why the DownVote?...Please leave a comment if your DownVote is genuine... Peace!! – Groove