MySQL REGEXP Operator

REGEXP operator in MySQL is used for pattern matching. It compares the given pattern in the input string and returns the result which is matching with the patterns. If this operator finds a match, the result is 1. Otherwise, the result is 0. This operator works the same as the REGEXP_LIKE() function.

Syntax

The following is a basic syntax to use this operator in MySQL:

In this syntax, the expression is an input string on which we will perform searching for matching the regular expression. And pattern represents the regular expression for which we are testing the string. This syntax is generally used with the SELECT statements.

Let us understand how this operator works in MySQL through examples.

Example

The below statement is the most basic regular expression where we have not uses any special characters. It means we have just used a string and compare if any part of the input string matches the pattern, it returns a match.

Here is the result:

Suppose we have a table named employee that contains the following data. Now, we will demonstrate various examples based on this table data.

If we want to search for an employee whose name start with j or s, we can do this as follows:

Executing the statement, we will get the desired result. See the below output:

If we want to get the employee whose name contains exactly four characters, we need to use the '^' and '$ meta-characters. These characters match the beginning and end of the employee name and repeat {4} times of any character '.' in-between as shown in the following statement:

Executing the statement, we will get the desired result. See the below output:

If we want to get the employee detail whose designation contains 'er' characters, we can do this by using the below query:

Executing the statement, we will get the desired result. See the below output:

If we want to get the employee name and designation whose name either contains p or s characters, we can do this by using the below query:

Executing the statement, we will get the desired result. See the below output: