MySQL RLIKE operator explained

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.