MySQL RLIKE operator explained

Posted on Nov 01, 2021

Learn how the MySQL RLIKE operator works

The MySQL RLIKE operator is used to perform a regular expression (regex) search in your table data.

The RLIKE operator is synonymous with the REGEXP_LIKE() function.

Let’s see the operator in action.

You can pass a regular expression enclosed in a quotation mark next to the RLIKE keyword as follows:

SELECT "William" RLIKE "iam";

The returned output will be as shown below:

+-----------------------+
| "William" RLIKE "iam" |
+-----------------------+
|                     1 |
+-----------------------+

You can use the RLIKE function to filter your table data and show only rows that match the regex pattern.

For example, suppose you have a

+----+---------------+
| id | name          |
+----+---------------+
|  1 | Mark Crane    |
|  2 | Natalia Smith |
|  3 | Gary Anderson |
|  4 | Joe Natsume   |
|  5 | Sarah         |
|  6 | Peter         |
|  7 | Nathan        |
+----+---------------+

You can use the RLIKE operator to show only names that contain Nat string as follows:

SELECT `name` 
  FROM students 
  WHERE `name` RLIKE 'nat';

The returned result set will be as follows:

+---------------+
| name          |
+---------------+
| Natalia Smith |
| Joe Natsume   |
| Nathan        |
+---------------+

The RLIKE operator will perform a case-insensitive search on your specified column.

If you need a case-sensitive search, then you need to use the REGEXP_LIKE() function instead of the RLIKE operator.

Here’s an example of a case-sensitive search with the REGEXP_LIKE() function:

SELECT `name` 
  FROM students 
  WHERE REGEXP_LIKE(`name`, 'nat', 'c');

The letter 'c' passed as the third argument of the REGEXP_LIKE() function will make MySQL to perform a case-sensitive search.

The list of options you can pass to the REGEXP_LIKE() function is as follows:

  • 'c': Case-sensitive search.
  • 'i': Case-insensitive search.
  • 'm': Multiple-line mode. Recognize line terminators within the string. The default behavior is to match line terminators only at the start and end of the string expression.
  • 'n': The . character matches line terminators. The default is for . matching to stop at the end of a line.
  • 'u': Unix-only line endings. Only the newline character is recognized as a line ending by the ., ^, and $ match operators.

For more information, you can check out the MySQL Regular Expressions documentation.

Level up your programming skills

I'm sending out an occasional email with the latest programming tutorials. Drop your email in the box below and I'll send new stuff straight into your inbox!

No spam. Unsubscribe anytime.