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 | Result |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
The following examples demonstrate how RLIKE
works when the global variable regexp_format
is set to 'advanced'
.
Call to | Result |
---|---|
|
|
|
|
|
|
|
|
|
|
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 | +------+------------+--------------------------+