MySQL – Pattern Matching Comparison Using Regular Expressions with REGEXP

MySQL supports pattern matching comparison using regular expressions with REGEXP keyword. In this post we will see how to use REGEXP in MySQL. We can use the patterns ^, $, | and sqaure braces to effectively match the values.

Let us create the following table to try regular expressions with REGEXP:

CREATE TABLE items(item_id INT, item_description VARCHAR(100));
INSERT INTO items VALUES (1,'Television');
INSERT INTO items VALUES (2,'Mobile');
INSERT INTO items VALUES (3,'laptop');
INSERT INTO items VALUES (4,'Cables');
INSERT INTO items VALUES (5,'Camera');
INSERT INTO items VALUES (6,'jewels');
INSERT INTO items VALUES (7,'shirt');
INSERT INTO items VALUES (8,'Cup');
INSERT INTO items VALUES (9,'Pen');
INSERT INTO items VALUES (10,'Pencil');

1 Find Out item_description That Starts with c Using Regular Expressions with REGEXP

SELECT item_description FROM items
WHERE item_description regexp '^c';

Result :

Item_description
 Cables
 Camera
 Cup

2 Find out item_description that ends with s
SELECT item_description FROM items
WHERE item_description regexp 's$';

Result :

Item_description
 Cables
 jewels

3 Find out item_description that starts with c or ends with s
SELECT item_description FROM items
WHERE item_description regexp '^c|s$';

Result :

Item_description
 Cables
 Camera
 jewels
 Cup

4 Find out item_description that contains the alphabet a
SELECT item_description FROM items
WHERE item_description regexp '[a]';

Result :

Item_description
 laptop
 Cables
 Camera

5 Find Out item_description That Contains the Alphabet c or p Using Regular Expressions with REGEXP

SELECT item_description FROM items
WHERE item_description regexp '^[cp]';

Result :

Item_description
 Cables
 Camera
 Cup
 Pen
 Pencil

A Few Notes Before You Use REGEXP

REGEXP returns true when the pattern matches anywhere in the value. That is why the anchors matter so much in the examples above. The caret ^ ties the pattern to the start of the value and the dollar sign $ ties it to the end. Without them, the pattern ‘c’ would match every value that has the letter c anywhere in it.

Notice also that ‘^c’ returned Cables, Camera and Cup even though the pattern uses a small c. With the default case insensitive collation, the comparison ignores case. If you need an exact case match, compare against a binary string or use a case sensitive collation.

Two more small things are good to know. RLIKE is a synonym for REGEXP, so you will see both in other people’s code. NOT REGEXP gives you the opposite list, which is handy when you want every row that does not follow a pattern. If you are on MySQL 8.0 or later, you also get functions like REGEXP_LIKE, REGEXP_REPLACE and REGEXP_SUBSTR for more advanced work.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.