The REGEXP Operator - Match regular expression |
|
| | REGEXP | Syntax: | String [NOT] REGEXP Pattern | | Return value: | INTEGER | | Synonyms: | RLIKE | | Function type: | Regular Expressions | |
| | The REGEXP 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 REGEXP function is also NULL. | SQL Examples for the REGEXP function |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
| SELECT 'test-abc-abc-abc' regexp 'TEST';
SELECT 'test-abc-abc-abc' not regexp 'TEST';
SELECT not 'test-abc-abc-abc' regexp 'TEST';
SELECT cast('test-abc-abc-abc' AS char CHARACTER SET utf8mb4) regexp cast('TEST' as char character set utf8mb4);
SELECT 'test-abc-ABC-aBc' regexp 'Ab';
SELECT 'test-abc-ABC-aBc' regexp '(?i)Ab';
SELECT 'test-abc-ABC-aBc' regexp '(?-i)Ab';
SELECT 'test' regexp '^[a-d]';
SELECT 'test' regexp '^[r-z]';
SELECT null regexp 'abc';
SELECT null not regexp 'abc';
SELECT 'test-aa-abc' regexp null;
|
|
| 'test-abc-abc-abc' regexp 'TEST' |
|---|
| int(1) |
|---|
| 0 |
|
|
| 'test-abc-abc-abc' not regexp 'TEST' |
|---|
| int(1) |
|---|
| 1 |
|
|
| not 'test-abc-abc-abc' regexp 'TEST' |
|---|
| int(1) |
|---|
| 1 |
|
|
| cast('test-abc-abc-abc' AS char CHARACTER SET utf8mb4) regexp cast('TEST' as char character set utf8mb4) |
|---|
| int(1) |
|---|
| 1 |
|
|
| 'test-abc-ABC-aBc' regexp 'Ab' |
|---|
| int(1) |
|---|
| 0 |
|
|
| 'test-abc-ABC-aBc' regexp '(?i)Ab' |
|---|
| int(1) |
|---|
| 1 |
|
|
| 'test-abc-ABC-aBc' regexp '(?-i)Ab' |
|---|
| int(1) |
|---|
| 0 |
|
|
| 'test' regexp '^[a-d]' |
|---|
| int(1) |
|---|
| 0 |
|
|
| 'test' regexp '^[r-z]' |
|---|
| int(1) |
|---|
| 1 |
|
|
| null regexp 'abc' |
|---|
| int(1) |
|---|
| NULL |
|
|
| null not regexp 'abc' |
|---|
| int(1) |
|---|
| NULL |
|
|
| 'test-aa-abc' regexp null |
|---|
| int(1) |
|---|
| NULL |
|
|
| | The examples were created with the MyWAY SQL manager: | How to use the REGEXP() function in MySQL and MariaDB databases | In MySQL and MariaDB, the REGEXP function is used to perform regular expression pattern matching. Strings matching a specific pattern can be searched for using regular expressions. Different regular expression patterns can be used to match different patterns in strings. Regular expressions provide a powerful way to define complex search patterns, including character classes, quantifiers, anchors, and more. Regular expression patterns are denoted by specific syntax and metacharacters.
The REGEXP function can be used in various applications, for example to filter data, to search for specific patterns or to validate input. By default, MySQL and MariaDB use the POSIX ERE syntax (Extended Regular Expressions). | | Further MySQL und MariaDB SQL Regular Expressions | | 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 |
| | RLIKE | String [NOT] RLIKE Pattern | | More about RLIKE Function |
|
|
| | | More information about the REGEXP SQL function: and |
|
|
|
|
