Use regular expressions in MySQL SELECT statements

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 (\\).

 

About Quinn McHenry

Quinn was one of the original co-founders of Tech-Recipes. He is currently crafting iOS applications as a senior developer at Small Planet Digital in Brooklyn, New York.
View more articles by Quinn McHenry

The Conversation

Follow the reactions below and share your own thoughts.

26 Responses to “Use regular expressions in MySQL SELECT statements”

  1. March 15, 2009 at 10:56 pm, Lawrence said:

    Thank you

    Reply

  2. July 07, 2009 at 6:05 pm, ////AE said:

    Is there a way to use regexp when defining a table to SELECT $something from?

    Reply

    • February 25, 2014 at 1:22 pm, Brett said:

      Same question. I’m hoping to return only certain information within a row, not the entire row itself. I want to use REGEX to filter the results, not just find the results.

      Reply

  3. November 13, 2009 at 5:33 am, Anonymous said:

    may i know Why this query showing false result

    select “hellooo” REGEXP “^h[el]+o$”;

    Reply

    • December 11, 2009 at 5:58 pm, Shane said:

      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$

      Reply

      • April 05, 2011 at 4:23 pm, Andy Mason said:

        Could also use the ‘+’ operator to match one or more o’s …

        select “hellooo” REGEXP “^h[el]+o+$”

        Reply

  4. January 07, 2010 at 6:33 pm, Wayne said:

    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

    Reply

    • January 08, 2010 at 6:29 pm, Anonymous said:

      @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).

      Reply

  5. August 05, 2010 at 10:17 am, Gumuruh S said:

    whoooooo… that’s cool!

    so anywa, how about we just want to select N until M row only?

    Reply

    • March 09, 2011 at 10:25 am, Josef Schalk said:

      was geht denn mitr dir

      Reply

  6. September 29, 2010 at 10:36 pm, GHead said:

    How to select from a column that not have character? like in php !preg_match(‘/[a-z]/i’)

    Reply

  7. February 19, 2011 at 5:25 pm, Gas said:

    sepp du depp

    Reply

  8. February 19, 2011 at 5:30 pm, Sepp-seppelin_constructions said:

    like und dann %
    seppelinsepp
    mcsepp
    fußballsepp
    keyboardsepp
    radfahrersepp
    sepp du depp

    Reply

  9. February 20, 2011 at 11:21 am, Nikn said:

    vwpanzersepp

    Reply

  10. February 20, 2011 at 11:47 am, Ojio said:

    ach sepp

    Reply

  11. February 23, 2011 at 9:00 am, viji said:

    hi

    Reply

  12. March 09, 2011 at 10:24 am, Jo said:

    moang

    Reply

  13. April 26, 2011 at 9:46 am, Gasr said:

    sepp

    Reply

  14. June 04, 2011 at 5:01 am, Hallo said:

    sepp du bist der depp

    Reply

  15. August 06, 2011 at 2:40 pm, Asdfasd said:

    I think you use the ^ character to negate stuff:
    [^a-z]
    would find anything that wasn’t a to z.

    Reply

  16. July 16, 2012 at 12:40 pm, hai said:

    SELECT *
    FROM words
    WHERE word REGEXP ‘^[able]+$’
    this query willl fetch all the words having these letters alone ie ab , able, abel, be.,like tat but it is also fetching bell , label ,bee, etc ie in input there is one l only but it also fetch the word having ll , ee like tat.
    Is there any way to remove duplicate letters in a word using regular exp in a query.

    Please do reply

    Reply

  17. October 19, 2012 at 2:25 pm, Ssekirime Geofrey said:

    Thank youuuuuuuuu. very much Quinn.

    Reply

  18. October 22, 2012 at 5:40 am, santosh said:

    SELECT ‘ÉÉÉÉÉÉÉÉÉÉÉÉÉÉÉÉÉÉÉÉÉÉÉÉ’ REGEXP ‘([ -~])*’
    above Query is returning true why and if i want false then what should be my regular Expression

    Reply

  19. February 20, 2014 at 7:41 am, Sergio Abreu said:

    It seems that [:upper:] doesn’t work properly. I tried used LOWER( field ) REGEXP ‘[[:upper:]]+’ and it matched….

    Reply

  20. February 20, 2014 at 8:38 am, Sergio Abreu said:

    I discovered the problem. MySql regexp [:upper:] is not taken seriously if the field colation is case-insensitive, what I think is a MySql fault.

    Reply

  21. April 10, 2014 at 7:18 am, Anand said:

    Nice one indeed.

    Reply

Leave a Reply