The RLIKE Operator - Match regular expression |
|
| | RLIKE | Syntax: | String [NOT] RLIKE Pattern | | Return value: | INTEGER | | Synonyms: | REGEXP | | Function type: | Regular Expressions | |
| | The RLIKE operator indicates whether the regular expression "Pattern" was found in "String".
If a match is found, the operator returns 1, if no match is found, the result is 0.
If the oprator is preceded by NOT, the result is reversed.
The regular expression "Pattern" does not have to be a literal string. It can also be specified as an expression, variable or table column.
If the value of "String" or "Pattern" is NULL, the return value of the RLIKE function is also NULL. | SQL Examples for the RLIKE function |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
| SELECT 'test-abc-abc-abc' rlike 'TEST';
SELECT 'test-abc-abc-abc' not rlike 'TEST';
SELECT not 'test-abc-abc-abc' rlike 'TEST';
SELECT cast('test-abc-abc-abc' AS char CHARACTER SET utf8mb4) rlike cast('TEST' as char character set utf8mb4);
SELECT 'test-abc-ABC-aBc' rlike 'Ab';
SELECT 'test-abc-ABC-aBc' rlike '(?i)Ab';
SELECT 'test-abc-ABC-aBc' rlike '(?-i)Ab';
SELECT 'test' rlike '^[a-d]';
SELECT 'test' rlike '^[r-z]';
SELECT null rlike 'abc';
SELECT null not rlike 'abc';
SELECT 'test-aa-abc' rlike null;
|
|
| 'test-abc-abc-abc' rlike 'TEST' |
|---|
| int(1) |
|---|
| 0 |
|
|
| 'test-abc-abc-abc' not rlike 'TEST' |
|---|
| int(1) |
|---|
| 1 |
|
|
| not 'test-abc-abc-abc' rlike 'TEST' |
|---|
| int(1) |
|---|
| 1 |
|
|
| cast('test-abc-abc-abc' AS char CHARACTER SET utf8mb4) rlike cast('TEST' as char character set utf8mb4) |
|---|
| int(1) |
|---|
| 1 |
|
|
| 'test-abc-ABC-aBc' rlike 'Ab' |
|---|
| int(1) |
|---|
| 0 |
|
|
| 'test-abc-ABC-aBc' rlike '(?i)Ab' |
|---|
| int(1) |
|---|
| 1 |
|
|
| 'test-abc-ABC-aBc' rlike '(?-i)Ab' |
|---|
| int(1) |
|---|
| 0 |
|
|
| 'test' rlike '^[a-d]' |
|---|
| int(1) |
|---|
| 0 |
|
|
| 'test' rlike '^[r-z]' |
|---|
| int(1) |
|---|
| 1 |
|
|
| null rlike 'abc' |
|---|
| int(1) |
|---|
| NULL |
|
|
| null not rlike 'abc' |
|---|
| int(1) |
|---|
| NULL |
|
|
| 'test-aa-abc' rlike null |
|---|
| int(1) |
|---|
| NULL |
|
|
| | The examples were created with the MyWAY SQL manager: | How to use the RLIKE() function in MySQL and MariaDB databases | In MySQL and MariaDB, the RLIKE() function, like the REGEXP function, can be used to perform regular expression pattern matching. Regular expressions can be used to search for strings that match a specific pattern. Different regular expression patterns can be combined to match different patterns in strings. Regular expressions provide a powerful way to define complex search patterns, such as character classes, quantifiers, anchors, and more. Regular expression patterns are denoted by specific syntax and metacharacters.
The RLIKE() function can be used in a variety of ways: filtering data, searching for specific patterns, validating input. | | Further MySQL und MariaDB SQL Regular Expressions | | REGEXP | String [NOT] REGEXP Pattern | | More about REGEXP Function |
| | REGEXP_INSTR | REGEXP_INSTR(String, Pattern [, Position] [, Occurrence] [, Return option] [, Match type]) | | More about REGEXP_INSTR Function |
| | REGEXP_LIKE | REGEXP_LIKE(String, Pattern [, Match type]) | | More about REGEXP_LIKE Function |
| | REGEXP_REPLACE | REGEXP_REPLACE(String, Pattern, Replace by [, Position] [, Occurrence] [, Match type]) | | More about REGEXP_REPLACE Function |
| | REGEXP_SUBSTR | REGEXP_SUBSTR(String, Pattern [, Position] [, Occurrence] [, Match type]) | | More about REGEXP_SUBSTR Function |
|
|
| | | More information about the RLIKE SQL function: and |
|
|
|
|
