Skip to main content

SingleStoreDB v7.6

RLIKE and REGEXP

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'.

Call to RLIKE

Result

SELECT 'adog' RLIKE 'o';

1

SELECT 'adog' RLIKE '.o';

1

SELECT 'dog58' RLIKE '[:digit:]';

1

SELECT '1dogbat' RLIKE '[:alpha:]';

1

SELECT 'rat' RLIKE 'b|c';

0

SELECT 'cat5mouse' RLIKE 'cat$';

0

SELECT 'dogcat2' RLIKE '^dog';

1

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

Call to RLIKE

Result

SELECT 'dog' RLIKE '\\D';

1

SELECT 'dog' RLIKE '\\d';

0

SELECT '26dog' RLIKE '\\W';

0

SELECT '26dog' RLIKE '\\w';

1

SELECT 'cat dog' RLIKE '\\mdog';

1

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 singlestore_docs_example;
CREATE DATABASE singlestore_docs_example;
USE singlestore_docs_example;
CREATE TABLE regexample_table(id INT, text_field TEXT);

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

SELECT id, text_field, text_field RLIKE '..the' FROM regexample_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 |
+------+------------+--------------------------+
In this section: