MySQL – Pattern Matching Comparison Using Regular Expressions with REGEXP

MySQL - Pattern Matching Comparison Using Regular Expressions with REGEXP regex 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 (https://blog.sqlauthority.com)

MySQL
Previous Post
SQL SERVER – What is the Difference Between Latches and Locks
Next Post
SQL SERVER – Performance: Do-it-Yourself Caching with Memcached vs. Automated Caching with SafePeak

Related Posts

5 Comments. Leave new

  • Hello Sir,
    Is sql server supports regular expressions, if it so please let me know.

    Reply
    • Yes. In SQL Server you just need to use LIKE instead of REGEXP and do not use ^,|, etc used in MySQL

      Reply
  • scott mcfadden
    March 17, 2014 3:21 pm

    Nice. Now show how to do regex in MSSQL ☺

    Reply
    • 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]%’;

      Reply
  • Justin Bannister
    March 19, 2014 6:58 pm

    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?

    Reply

Leave a Reply