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:

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
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

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

Result :

Item_description
 Cables
 Camera
 Cup
 Pen
 Pencil

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads