Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

regex
sql

How to create regular expressions in SQL

Educative Answers Team

A regular expression is a sequence of characters used to match a pattern to a string. You can use it for searching text and validating input.

Remember that a regular expression is not​ the property of a particular language.

A regular expression must be enclosed within single quotes. Doing so ensures that the entire expression is interpreted by the SQL function.

Metacharacters supported in regular expressions

Character Description
. Matches any character.
+ Matches one or more occurrences of the preceding subexpression.
? Matches zero or one occurrence of the preceding subexpression.
* Matches zero or more occurrences of the preceding subexpression.
{m} Matches exactly mm occurrences of the preceding subexpression.
{m,} Matches at least mm occurrences of the preceding subexpression.
{m,n} Matches at least mm, but not more than nn, occurrences of the preceding subexpression.
[ … ] Matches any character in the list.
[ ^ … ] Matches any character not in the list.
\ Treats the subsequent metacharacter in the expression as a literal.
^ Match the subsequent expression only when it occurs at the beginning of a line.
$ Match the preceding expression only when it occurs at the end of a line.

Creating regular expressions in SQL

MySQL allows you to match patterns by using the REGEXP operator.

See the syntax below:

SELECT 
    column_list
FROM
    table_name
WHERE
    string_column REGEXP pattern;

Examples

Consider the following employees table:

CREATE TABLE employees(
  emp_id int,
  emp_name varchar(20),
  dept varchar(20),
  age int
);


INSERT INTO employees(emp_id,emp_name,dept,age)
VALUES(1, "John", "Intern",25);

INSERT INTO employees(emp_id,emp_name,dept,age)
VALUES(5, "jake", "Intern",25);

INSERT INTO employees(emp_id,emp_name,dept,age)
VALUES(2, "David", "Intern",30);

INSERT INTO employees(emp_id,emp_name,dept,age)
VALUES(3, "Mike", "Engineer",29);

INSERT INTO employees(emp_id,emp_name,dept,age)
VALUES(4, "Alex", "HR",27);
id name dept age
1 John Intern 25
2 David Intern 30
3 Mike Engineer 29
4 Alex HR 27
5 jake Intern 25

Take a look at the examples below; they show how to create regular expressions (and use them) with the REGEXP operator.

If you want the REGEXP operator to compare strings in case-sensitive fashion, you can use the BINARY operator to cast a string to a binary string

-- To find employees whose names start with the character j, use 
-- the metacharacter '^' to match at the beginning of the name.
SELECT 
    emp_name
FROM
    employees
WHERE
    emp_name REGEXP '^j';

-- matches only uppercase "J" at the beginning of the product name.
SELECT 
    emp_name
FROM
    employees
WHERE
    emp_name REGEXP BINARY '^J';

-- To find the product whose name ends with f, use 'f$' to 
-- match the end of a string.
SELECT 
    emp_name
FROM
    employees
WHERE
    emp_name REGEXP 'e$';

-- To find the product whose name contains the letter "k", 
-- use the following query:
SELECT 
    emp_name
FROM
    employees
WHERE
    emp_name REGEXP 'k';

-- To find the product whose name contains exactly 10 characters, 
-- use ‘^' and ‘$ to match the beginning and end of the product name, 
-- and repeat {10} times of any character ‘.' in between as shown 
-- in the following query:
SELECT 
    emp_name
FROM
    employees
WHERE
    emp_name REGEXP '^.{10}$';


RELATED TAGS

regex
sql
Copyright ©2022 Educative, Inc. All rights reserved
RELATED COURSES

View all Courses

Keep Exploring