CODEWITHSUNDEEP
mysqlregex
Tiny
Tiny

Reputation: 27899

Regular expressions in MySql

In MySql (I'm using 5.1.48), the following regular expressions return true i.e 1.

SELECT '10-5' REGEXP '10-5' as temp;
SELECT '10/5' REGEXP '10/5' as temp;
SELECT '1*5' REGEXP '1*5' as temp;

The following expressions however return false i.e 0.

SELECT '10+5' REGEXP '10+5' as temp;
SELECT '10*5' REGEXP '10*5' as temp;

To use a literal instance of a special character in a regular expression, precede it by two backslash (\) characters. The MySQL parser interprets one of the backslashes, and the regular expression library interprets the other.

Escaping + and * in the preceding two statements returns true i.e 1 as follows.

SELECT '10+5' REGEXP '10\\+5' as temp;
SELECT '10*5' REGEXP '10\\*5' as temp;

If this is the case then why is * in the following statement (the last one in the first snippet) not required to escape?

SELECT '1*5' REGEXP '1*5' as temp;

It returns true i.e 1 without escaping * and the following something similar (the last one in the second snippet) returns false.

SELECT '10*5' REGEXP '10*5' as temp;

It requires * to be escaped. Why?

Upvotes: 0

Views: 95

Answers (1)

Alain Collins
Alain Collins

Reputation: 16362

An unescaped asterisk, as you know, means "zero or more of the preceeding character", so "1*5" means "any number of 1s, followed by a 5".

The key is this info from the doc:

 A REGEXP pattern match succeeds if the pattern matches anywhere in the value being tested. (This differs from a LIKE pattern match, which succeeds only if the pattern matches the entire value.)

So, "1*5" ("any number of 1s, followed by a 5") will match the string "1*5" by only seeing the "5". "10*5" ("1, followed by any number of 0s, followed by a 5") won't match the string "10*5" because the "*" character breaks it up.

Hope that helps.

Upvotes: 2

Related Questions