Using Regular Expressions in MySQL

Searching for data in a MySQL SELECT statement means incorporating a WHERE clause. It's easy to search for a single item:

SELECT prodid, product WHERE prodid = 100;

What gets tricky is if you need to search for an item based on search pattern. MySQL uses the LIKE clause to help out with that:

SELECT prodid, product WHERE product LIKE 'apple%';

The percent sign works as a wildcard character, matching zero or more characters in the string. So this will return apples, as well as apple juice.

The wildcard character in the LIKE operator is handy, but it's somewhat limited. A more advanced way of searching for data is using a regular expression pattern match. Regular expressions use their own language to define a template used to match data patterns.

MySQL supports regular expressions using the REGEXP operator. You specify the matching pattern similar to how you do it with the LIKE operator:

SELECT prodid, product WHERE product REGEXP 'apple';

In the regular expression, by default any text you enter is matched anywhere in the data field. So, this query will return apple, apple juice, or candy apple.

You can specify exactly where in the string the text pattern should appear by using anchor characters. The caret character (^) indicates the start of the string. So, the following query matches only apple and apple juice:

SELECT prodid, product WHERE product REGEXP '^apple';

The dollar sign indicates the end of the string and would return apple or candy apple:

SELECT prodid, product WHERE product REGEXP 'apple$';

The MySQL regular expression languages uses lots of special characters to define the matching template. The following table shows the more popular ones.

With the LIKE and REGEXP operators in the WHERE clause, you can customize your SELECT statement to look for just about any type of data that you need for your application!