Non-greedy wildcard?

[jwcarlton]

I've been trying to find this, but I'm not sure that I'm using the
right terminology. Sorry if this is an FAQ.

I'm trying to SELECT something using a non-greedy wildcard. I know to
use % for a greedy wildcard, but how would I correctly do the
following in MySQL?

%<img(.*?)src="*(\s*)<a%

Reading it out, I'm searching for anything, followed by <img, followed
by any character (or no character) preceding src=, followed by " or no
", followed by a whitespace or no whitespace, followed by <a, followed
by anything else.

TIA,

Jason

[onedbguru]

You mean - use a regexp??

http://dev.mysql.com/doc/refman/5.1/en/regexp.html

select * from table where col1 REGEXP '%<img(.*?)src="*(\s*)<a%';

[jwcarlton]

>  You mean - use a regexp??
>
> http://dev.mysql.com/doc/refman/5.1/en/regexp.html
>
> select * from table where col1 REGEXP '%<img(.*?)src="*(\s*)<a%';

HA! I should have known it would be something as simple as that.

I'm getting an error using that, but I'm sure I'll figure it out. The
error is:

'repetition-operator operand invalid' from regexp

[jwcarlton]

Narrowing it down, I'm still getting the same error on this:

SELECT * FROM table WHERE col1 REGEXP 'img(.*?)'

The "repetition-operator" would have to be the *, right? According to
the dev.mysql.com link above, this is valid?

[jwcarlton]

For anyone else reading this, the ? apparently doesn't translate to
"not greedy" in MySQL; I removed it, and the regex worked fine. So,
while REGEXP did work for this particular application, I'm assuming
that it's still greedy, so it doesn't exactly work in all applications.

["Álvaro G. Vicario"]

MySQL uses POSIX style while preg_* PHP functions use Perl style. You'll
also miss stuff like \s (you have to use [[:space:]] instead). Here's
the basic reference:

http://dev.mysql.com/doc/refman/5.1/en/regexp.html

You can probably google for "man 7 regex" and find more information.

--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

[Axel Schwenke]

jwcarlton <jwca...@gmail.com> wrote:
> I've been trying to find this, but I'm not sure that I'm using the
> right terminology. Sorry if this is an FAQ.
>
> I'm trying to SELECT something using a non-greedy wildcard. I know to
> use % for a greedy wildcard, but how would I correctly do the
> following in MySQL?
>
> %<img(.*?)src="*(\s*)<a%

Not? Because MySQL has no way to return matched substrings?

When it comes to matching as such, then greedyness does not matter.
Each wildcard is as greedy as necessary to make the match.

Example1:

mysql> -- here the first % matches 'cdef'
mysql> select 'abcdefdefg' like 'ab%defg%';
+------------------------------+
| 'abcdefdefg' like 'ab%defg%' |
+------------------------------+
| 1 |
+------------------------------+

mysql> -- here the first % can match 'c' or 'cdef'
mysql> -- does not matter unless we ask for the matched substrings
mysql> select 'abcdefdefg' like 'ab%def%';
+-----------------------------+
| 'abcdefdefg' like 'ab%def%' |
+-----------------------------+
| 1 |
+-----------------------------+
1 row in set (0,00 sec)

Example2:

mysql> SELECT 'foo<img src=bar.jpg><a blubb' LIKE '%<img%';
+----------------------------------------------+
| 'foo<img src=bar.jpg><a blubb' LIKE '%<img%' |
+----------------------------------------------+
| 1 |
+----------------------------------------------+
1 row in set (0,00 sec)

mysql> SELECT 'foo<img src=bar.jpg><a blubb' LIKE '%<img%src=%';
+---------------------------------------------------+
| 'foo<img src=bar.jpg><a blubb' LIKE '%<img%src=%' |
+---------------------------------------------------+
| 1 |
+---------------------------------------------------+
1 row in set (0,00 sec)

mysql> SELECT 'foo<img src=bar.jpg><a blubb' LIKE '%<img%src=%<a%';

+------------------------------------------------------+
| 'foo<img src=bar.jpg><a blubb' LIKE '%<img%src=%<a%' |
+------------------------------------------------------+
| 1 |
+------------------------------------------------------+
1 row in set (0,00 sec)

XL