30

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?

Taryn
  • 242,637
  • 56
  • 362
  • 405
BackSlash
  • 21,927
  • 22
  • 96
  • 136

2 Answers2

63

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.

NullUserException
  • 83,810
  • 28
  • 209
  • 234
1

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

ErickBest
  • 4,586
  • 5
  • 31
  • 43