REGEXP in MySQL

A regular expression is a powerful way of specifying a pattern for a complex search. It performs a pattern match of a string expression against a pattern. The pattern (search condition) is supplied as an argument. If the pattern finds a match in the expression, the function returns 1, else it returns 0. If either expression or pattern is NULL, the function returns NULL.

Regular expressions can be used to seek a given value from an email, IP address, phone number, json, or a string containing special characters, or from anything else that follows a predetermined pattern.

Here is an example.

DROP TABLE IF EXISTS Dept;

CREATE TABLE Dept(
EmployeeDetails VARCHAR(100));

INSERT INTO Dept VALUES
(‘John|Sales Team’),
(‘Mike|Sales’),
(‘Smith|Development’),
(‘Jim|New Sales Team’),
(‘Lucy|Development’),
(‘Will|Development’);

SELECT * FROM Dept WHERE EmployeeDetails regexp ‘Sales’;

— Match zero or one instance of the strings preceding it(?):
— Returns all the rows containing ‘Sales’.
SELECT * FROM Dept WHERE EmployeeDetails regexp ‘Sales?’;

The above two queries return the following
John|Sales Team
Mike|Sales
Jim|New Sales Team

— Match the end of a string($): Gives all the names ending with ‘Sales’.
SELECT * FROM Dept WHERE EmployeeDetails regexp ‘Sales$’;

The above query returns the following
— Mike|Sales

— matches any of the patterns based on the given parameters.
SELECT * FROM Dept WHERE EmployeeDetails regexp ‘John|Smith’;
It returns the following
John|Sales Team
Smith|Development