RLIKE and REGEXP are synonyms. RLIKE is discussed below. The discussion also applies to REGEXP, as both functions work identically.

RLIKE searches a string for a regular expression pattern. It returns 1 if the pattern is found and returns 0 otherwise.

RLIKE supports the extended and advanced regular expression formats. You can set the global variable regexp_format to specify which format you want to use.

Syntax

source_string RLIKE pattern
source_string REGEXP pattern

source_string

A string expression, such as a column name, to be searched.

pattern

A string literal representing a regular expression.

Examples

The following examples demonstrate how RLIKE works when the global variable regexp_format is set to 'extended'.

The following examples demonstrate how RLIKE works when the global variable regexp_format is set to 'advanced'.

Using RLIKE With a Table

The following example shows how to call RLIKE once per table row. This example uses extended regular expression matching. Advanced regular expression matching could be used as well.

SET GLOBAL regexp_format = 'extended';

DROP DATABASE IF EXISTS memsql_docs_example;
CREATE DATABASE memsql_docs_example;
USE memsql_docs_example;
CREATE TABLE example_table(id INT, text_field TEXT);

INSERT INTO example_table VALUES (1, 'the'), (2, 'athe'), (3, 'abthe'),
(4, 'abcthe'), (5, 'thethe');

SELECT id, text_field, text_field RLIKE '..the' FROM example_table
ORDER BY id;

Output

+------+------------+--------------------------+
| id   | text_field | text_field RLIKE '..the' |
+------+------------+--------------------------+
|    1 | the        |                        0 |
|    2 | athe       |                        0 |
|    3 | abthe      |                        1 |
|    4 | abcthe     |                        1 |
|    5 | thethe     |                        1 |
+------+------------+--------------------------+

Was this Article Helpful?

Thanks For Your Feedback!