MacLochlainns Weblog

We had an interesting set of questions regarding the REGEXP comparison operator in MySQL today in both sections of Database Design and Development. They wanted to know the default behavior.

For example, we built a little movie table so that we didn’t change their default sakila example database. The movie table was like this:

CREATE TABLE movie
( movie_id     int unsigned primary key auto_increment
, movie_title  varchar(60)) auto_increment=1001;

Then, I inserted the following rows:

INSERT INTO movie 
( movie_title )
VALUES
 ('The King and I')
,('I')
,('The I Inside')
,('I am Legend');

Querying all results with this query:

SELECT * FROM movie;

It returns the following results:

+----------+----------------+
| movie_id | movie_title    |
+----------+----------------+
|     1001 | The King and I |
|     1002 | I              |
|     1003 | The I Inside   |
|     1004 | I am Legend    |
+----------+----------------+
4 rows in set (0.00 sec)

The following REGEXP returns all the rows because it looks for a case insensitive “I” anywhere in the string.

SELECT movie_title
FROM   movie
WHERE  movie_title REGEXP 'I';

The implicit regular expression is actually:

WHERE  movie_title REGEXP '^.*I.*$';

It looks for zero-to-many of any character before and after the “I“. You can get any string beginning with an “I” with the “^I“; and any string ending with an “I” with the “I$“. Interestingly, the “I.+$” should only match strings with one or more characters after the “I“, but it returns:

+----------------+
| movie_title    |
+----------------+
| The King and I |
| The I Inside   |
| I am Legend    |
+----------------+
3 rows in set (0.00 sec)

This caught me by surprise because I was lazy. As pointed out in the comment, it only appears to substitute a “.*“, or zero-to-many evaluation for the “.+” because it’s a case-insensitive search. There’s another lowercase “i” in the “The King and I” and that means the regular expression returns true because that “i” has one-or-more following characters. If we convert it to a case-sensitive comparison with the keyword binary, it works as expected because it ignores the lowercase “i“.

WHERE  binary movie_title REGEXP '^.*I.*$';

This builds on my 10-year old post on Regular Expressions. As always, I hope these notes helps others discovering features and behaviors of the MySQL database, and Bill thanks for catching my error.