MySQL - REGEXP Operator

The MySQL REGEXP operator is used for pattern matching using regular expressions. It returns 1 if there is a match, and 0 if there is none. This operator is similar to the REGEXP_LIKE() function, but provides additional options such as case-insensitivity and Unicode character ranges.

A regular expression (also called as regex or regexp) is a sequence of characters that forms a search pattern. They can be used to perform all types of text search and text replace operations.

MySQL offers various functions and operators for working with regular expressions, including REGEXP, REGEXP_REPLACE, REGEXP_INSTR, REGEXP_SUBSTR, and REGEXP_LIKE. These functions allow you to search for patterns in strings, replace or modify text within strings, extract substrings that match a specified regular expression pattern, and determine whether a string matches a given regular expression pattern.

Syntax

Following is the syntax of MySQL REGEXP operator −

expr REGEXP pat

It has two operands −

  • expr − A string value (left) in which you want to carry on the search.

  • pat − A regular expression (right) for which you want to search.

Example

Let's start with a simple example where we check if the string "Welcome to Tutorialspoint" contains the pattern "to" −

SELECT 'Welcome to Tutorialspoint' REGEXP 'to';

Following is the output of the above code −

'Welcome to Tutorialspoint' REGEXP 'to'
1

Example

In this case, we're searching for the pattern "is" within the string "This is a sample string."−

SELECT 'This is a sample string' REGEXP 'is';

Output of the above code is as shown below −

'This is a sample string' REGEXP 'is'
1

Example

You can also use the RLIKE operator as an alternative to REGEXP. It provides the same result as REGEXP. Here, we check for the pattern "unknown" in the string "Welcome to Tutorialspoint."

If the given pattern is not found in the string, this operator returns 0 −

SELECT 'Welcome to Tutorialspoint' REGEXP 'unknown';

The output obtained is as follows −

'Welcome to Tutorialspoint' REGEXP 'unknown';
0

Here is another example −

SELECT 'This is a sample string' RLIKE 'value';

The result produced is as follows −

'This is a sample string' RLIKE 'value';
0

Example

If either of the first two operands is NULL, this operator returns NULL. Here, we check if NULL matches the pattern "value" −

SELECT NULL REGEXP 'value';

We get the output as follows −

NULL REGEXP 'value'
NULL

Following is another example −

SELECT 'Tutorialspoint' REGEXP NULL;

Following is the output of the above code −

'Tutorialspoint' REGEXP NULL
NULL

Example

You can use the NOT clause to reverse the results. It returns 0 in case of a match and 1 if there's no match.

In this example, we check if the pattern "is" is NOT present in the string "This is a sample string" −

SELECT NOT 'This is a sample string' REGEXP 'is';

Output of the above code is as shown below −

NOT 'This is a sample string' REGEXP 'is'
0

Following is another example −

SELECT NOT 'Welcome to Tutorialspoint' REGEXP 'unknown';

The result obtained is as follows −

NOT 'Welcome to Tutorialspoint' REGEXP 'unknown'
1

Example

Suppose we have created a table named "EMP_TABLE" in the database using the CREATE statement and inserted three records in it as shown below −

CREATE TABLE EMP_TABLE(
   ID VARCHAR(5), 
   Name VARCHAR(255), 
   DOB DATE, 
   Location VARCHAR(255)
);

Now, let us insert records into it using the INSERT statement −

INSERT INTO EMP_TABLE VALUES 
('101', 'Amit', DATE('1970-01-08'), 'Hyderabad'),
('102','Sumith', DATE('1990-11-02'), 'Vizag'),
('105','Sudha', DATE('1980-11-06'), 'Delhi'),
('MyID2','Raja', DATE('1980-11-06'), 'Goa'),
('109','Javed', DATE('1980-11-06'), 'Pune'),
('AA', 'Suma', DATE('1980-11-06'), 'Chennai'),
('120','Vani', DATE('1980-11-06'), 'Delhi'),
('oo1','Devi', DATE('1980-11-06'), 'Goa');

Following query displays the records which have ID values containing only digits −

SELECT * FROM EMP_TABLE where ID REGEXP '^[0-9]{3}$';

After executing the above code, we get the following output −

ID NAME DOB Location
101 Amit 1970-01-08 Hyderabad
102 Sumith 1990-11-02 Vizag
105 Sudha 1980-11-06 Delhi
109 Javed 1980-11-06 Pune
120 Vani 1980-11-06 Delhi

Following query displays the records of EMP_TABLE whose name start with the letter "s" −

SELECT * FROM EMP_TABLE where Name REGEXP '^[s]';

The result produced is as shown below −

ID NAME DOB Location
102 Sumith 1990-11-02 Vizag
105 Sudha 1980-11-06 Delhi
AA Suma 1980-11-06 Chennai

Following query displays all the records whose name contains either "Raja" or "Roja" −

SELECT * FROM EMP_TABLE where Name REGEXP 'Raja|Roja';

The output produced is as shown below −

ID NAME DOB Location
MyID2 Raja 1980-11-06 Goa
mysql-regexp-operator.htm
Advertisements