Use regular expressions in MySQL SELECT statements
Contributed by qmchenry 
Tagged: MySQL A very cool and powerful capability in MySQL and other databases is the ability to incorporate regular expression syntax when selecting data. The regular expresion support in MySQL is extensive. This recipe reviews regular expression use in MySQL and lists the supported regular expression metacharacters.
The basic syntax to use regular expressions in a MySQL query is:
SELECT something FROM table WHERE column REGEXP 'regexp'
For example, to select all columns from the table events where the values in the column id end with 5587, use:
SELECT * FROM events WHERE id REGEXP '5587$'
A more elaborate example selects all columns of the table reviews where the values in the column description contain the word excellent:
SELECT * FROM reviews WHERE description REGEXP '[[:<:]]excellent[[:>:]]'
MySQL allows the following regular expression metacharacters:
. match any character
? match zero or one
* match zero or more
+ match one or more
{n} match n times
{m,n} match m through n times
{n,} match n or more times
^ beginning of line
$ end of line
[[:<:]] match beginning of words
[[:>:]] match ending of words
[:class:] match a character class
i.e., [:alpha:] for letters
[:space:] for whitespace
[:punct:] for punctuation
[:upper:] for upper case letters
[abc] match one of enclosed chars
[^xyz] match any char not enclosed
| separates alternatives
MySQL interprets a backslash (\) character as an escape character. To use a backslash in a regular expression, you must escape it with another backslash (\\).
You may also like -
MySQL: Use CONCAT to include text in SELECT results
Display a list of databases on a MySQL server
Drop or delete a table in MySQL
Mimic The MySQL LIMIT Feature in Microsoft SQL Server
Connect to a MySQL server using the mysql command
Import CSV file directly into MySQL
Delete a column from an existing MySQL table
Solve: Can't connect to local MySQL server through socket /tmp/mysql.sock
Lawrence said on March 15, 2009
Thank you
////AE said on July 7, 2009
Is there a way to use regexp when defining a table to SELECT $something from?
Anonymous said on November 13, 2009
may i know Why this query showing false result
select “hellooo” REGEXP “^h[el]+o$”;
Shane said on December 11, 2009
santoshb,
i don’t know if you have the answer to your question yet, but your regexp is only look for one ‘o’. The $ sign means the absolute end of the string.
You could either do:
^h[el]+o
or..
^h[el]+ooo$
Wayne said on January 7, 2010
I’ve been using mySQL for years, and I’ve been using regular expressions for years. Yet, somehow, I never found out until just now (from this recipe) that mySQL supported them in a manner like this. That’s amazingly useful. Thank you!
(Of course, I also see that you wrote this recipe almost six years ago – so thank you for six years ago!) :)
Does REGEXP have a bigger or smaller or identical performance hit when compared to using LIKE for substrings?
For instance, here’s a couple pairs of otherwise equivalent queries. Within each pair, which would be better for performance?
SELECT * FROM events WHERE id REGEXP ‘abcd’
SELECT * FROM events WHERE id like ‘%abcd%’
SELECT * FROM events WHERE id REGEXP ‘abcd.*lmnop’
SELECT * FROM events WHERE id like ‘%abcd%lmnop%’
Is LIKE just a specialize synonym for REGEXP which actually uses REGEXP in the background, or is it truly it’s own function? (Asked because if it uses REGEXP, then the performance hits should be identical within both those pairs.)
Anyway, thanks again for adding a weapon to my arsenal that should have been there years ago. I can’t imagine how I’ve overlooked it for so long.
w
Anonymous said on January 8, 2010
@Wayne
I ran the following queries three times each on my MySQL database which has over 200,000 articles in it:
SELECT * FROM `mos_content` WHERE title REGEXP ‘article’;
SELECT * FROM `mos_content` WHERE title LIKE ‘%article%’;
REGEXP averaged 0.047333333 seconds
LIKE averaged 0.035333333 seconds
There was hardly any deviation between the runs. But it’s obvious that LIKE is about 25% faster than REGEXP for some reason (at least on my specific MySQL configuration).
Gumuruh S said on August 5, 2010
whoooooo… that’s cool!
so anywa, how about we just want to select N until M row only?
GHead said on September 29, 2010
How to select from a column that not have character? like in php !preg_match(‘/[a-z]/i’)
Gas said on February 19, 2011
sepp du depp
Sepp-seppelin_constructions said on February 19, 2011
like und dann %
seppelinsepp
mcsepp
fußballsepp
keyboardsepp
radfahrersepp
sepp du depp
Nikn said on February 20, 2011
vwpanzersepp
Ojio said on February 20, 2011
ach sepp
viji said on February 23, 2011
hi
Jo said on March 9, 2011
moang
Josef Schalk said on March 9, 2011
was geht denn mitr dir
Andy Mason said on April 5, 2011
Could also use the ‘+’ operator to match one or more o’s …
select “hellooo” REGEXP “^h[el]+o+$”
Gasr said on April 26, 2011
sepp
Hallo said on June 4, 2011
sepp du bist der depp
Asdfasd said on August 6, 2011
I think you use the ^ character to negate stuff:
[^a-z]
would find anything that wasn’t a to z.