Regular Expressions REGEXP

Regular expressions are created using the regular expression language, a specialized language designed to:

extract phone numbers

to locate all files with digits in the start, middle or end of their names

to find all repeated words in a block of text

to replace all URLs in a page with actual HTML links to those same URLs

and much more

Like any language, regular expressions have a special syntax and instructions that you must learn.

So what does this have to do with MySQL? As already explained, all regular expressions do is match text, comparing a pattern (the regular expression) with a string of text. MySQL provides rudimentary support for regular expressions with WHERE clauses, allowing you to specify regular expressions that are used to filter data retrieved using SELECT.

This will all become much clearer with some examples.

Basic Character Matching

We'll start with a very simple example. The following statement retrieves all rows where column prod_name contains the text 1000:

SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000'
ORDER BY prod_name;

This statement looks much like the ones that used LIKE except that the keyword LIKE has been replaced with REGEXP. This tells MySQL that what follows is to be treated as a regular expression (one that just matches the literal text 1000).

So, why bother using a regular expression? Well, in the example just used, regular expressions really add no value (and probably hurt performance), but consider this next example:

SELECT prod_name
FROM products
WHERE prod_name REGEXP '.000'
ORDER BY prod_name;