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';
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$';
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$';
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]';
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 (https://blog.sqlauthority.com)
5 Comments. Leave new
Hello Sir,
Is sql server supports regular expressions, if it so please let me know.
Yes. In SQL Server you just need to use LIKE instead of REGEXP and do not use ^,|, etc used in MySQL
Nice. Now show how to do regex in MSSQL ☺
For the first example, SQL Server query would be
SELECT item_description FROM items
WHERE item_description Like ‘[c]%’;
For fifth example, it would be
SELECT item_description FROM items
WHERE item_description LIKE ‘[cp]%’;
This looks like a useful feature. What performance impact does this have on the query? Does MySql come with any tools for composing more complex regular expressions?