The REGEXP_SUBSTR SQL Function in MySQL and MariaDB - Substring by regular expression |
|
| | REGEXP_SUBSTR | Syntax: | REGEXP_SUBSTR(String, Pattern [, Position] [, Occurrence] [, Match type]) | | Return value: | CHAR | | Function type: | Regular Expressions | |
| | The REGEXP_SUBSTR() function returns the portion of "String" that matches the regular expression of "Pattern".
If the "Pattern" is not found, the function returns an empty string on MariaDB 10.3, while on MySQL 8.0 the result is NULL.
The function respects the case-sensitive rules of the effective sorting.
If the value of "String" or "Pattern" is NULL, the return value of the REGEXP_SUBSTR() function is also NULL.
In MySQL, REGEXP_INSTR() accepts the following optional arguments: These options are not available in MariaDB.
"Position": The position in "String" at which to start the search. The default is 1 if not specified.
"Occurrence": What occurrence of a match to search for. The default is 1 if not specified.
"Match type": A string specifying how the match is performed:
| Type | Description |
| c | Case sensitive. |
| i | Case-insensitive match. |
| m | Multiline mode. Line terminators within the string are recognized. The default behavior is to match line terminators only at the beginning and end of "String". |
| n | A point "." is interpreted as a line terminator: Default. |
| u | Unix-only line endings. Only the newline character is recognized as a newline by the ".", ",", "^" and "$" match operators. |
If characters are specified within "Match type" that result in conflicting options, the character on the right takes precedence. | Examples of the REGEXP_SUBSTR() function on MariaDB 10.3 |
| SELECT REGEXP_SUBSTR('test-abc-abc-abc', 'Test');
SELECT REGEXP_SUBSTR(cast('test-abc-abc-abc' as char character set utf8mb4), cast('Test' as char character set utf8mb4));
SELECT REGEXP_SUBSTR('test-abc-ABC-aBc', '(?i)abc');
SELECT REGEXP_SUBSTR('test-abc-ABC-aBc', '(?i)ABC');
SELECT REGEXP_SUBSTR('test-abc-ABC-aBc', '(?-i)ABC');
SELECT REGEXP_SUBSTR(null, 'abc');
SELECT REGEXP_SUBSTR('test-aa-abc', null);
|
|
| REGEXP_SUBSTR('test-abc-abc-abc', 'Test') |
|---|
| varchar(16) BINARY |
|---|
| |
|
|
| REGEXP_SUBSTR(cast('test-abc-abc-abc' as char character set utf8mb4), cast('Test' as char character set utf8mb4)) |
|---|
| varchar(16) BINARY |
|---|
| test |
|
|
| REGEXP_SUBSTR('test-abc-ABC-aBc', '(?i)abc') |
|---|
| varchar(16) BINARY |
|---|
| abc |
|
|
| REGEXP_SUBSTR('test-abc-ABC-aBc', '(?i)ABC') |
|---|
| varchar(16) BINARY |
|---|
| abc |
|
|
| REGEXP_SUBSTR('test-abc-ABC-aBc', '(?-i)ABC') |
|---|
| varchar(16) BINARY |
|---|
| ABC |
|
|
| REGEXP_SUBSTR(null, 'abc') |
|---|
| varchar(0) BINARY |
|---|
| NULL |
|
|
| REGEXP_SUBSTR('test-aa-abc', null) |
|---|
| varchar(11) BINARY |
|---|
| NULL |
|
|
| Examples of the REGEXP_SUBSTR() function on MySQL 8.0 |
1
2
3
4
5
6
7
8
9
10
11
12
13
|
| SELECT REGEXP_SUBSTR('test-abc-abc-abc', 'Test');
SELECT REGEXP_SUBSTR(cast('test-abc-abc-abc' as char character set utf8mb4), cast('Test' as char character set utf8mb4));
SELECT REGEXP_SUBSTR('test-abc-ABC-aBc', '(?i)abc');
SELECT REGEXP_SUBSTR('test-abc-ABC-aBc', '(?i)abc', 7);
SELECT REGEXP_SUBSTR('test-abc-ABC-aBc', '(?i)abc', 1, 2);
SELECT REGEXP_SUBSTR('test-abc-ABC-aBc', '(?i)abc', 1, 3);
SELECT REGEXP_SUBSTR('test-abc-ABC-aBc', 'abc', 1, 2, 'i');
SELECT REGEXP_SUBSTR(null, 'abc');
SELECT REGEXP_SUBSTR('test-aa-abc', null);
|
|
| REGEXP_SUBSTR('test-abc-abc-abc', 'Test') |
|---|
| varchar(16) BINARY |
|---|
| NULL |
|
|
| REGEXP_SUBSTR(cast('test-abc-abc-abc' as char character set utf8mb4), cast('Test' as char character set utf8mb4)) |
|---|
| varchar(16) BINARY |
|---|
| test |
|
|
| REGEXP_SUBSTR('test-abc-ABC-aBc', '(?i)abc') |
|---|
| varchar(16) BINARY |
|---|
| abc |
|
|
| REGEXP_SUBSTR('test-abc-ABC-aBc', '(?i)abc', 7) |
|---|
| varchar(16) BINARY |
|---|
| ABC |
|
|
| REGEXP_SUBSTR('test-abc-ABC-aBc', '(?i)abc', 1, 2) |
|---|
| varchar(16) BINARY |
|---|
| ABC |
|
|
| REGEXP_SUBSTR('test-abc-ABC-aBc', '(?i)abc', 1, 3) |
|---|
| varchar(16) BINARY |
|---|
| aBc |
|
|
| REGEXP_SUBSTR('test-abc-ABC-aBc', 'abc', 1, 2, 'i') |
|---|
| varchar(16) BINARY |
|---|
| ABC |
|
|
| REGEXP_SUBSTR(null, 'abc') |
|---|
| varchar(0) BINARY |
|---|
| NULL |
|
|
| REGEXP_SUBSTR('test-aa-abc', null) |
|---|
| varchar(11) BINARY |
|---|
| NULL |
|
|
| | The examples were created with the MyWAY SQL manager: | How to use the REGEXP_SUBSTR() function in MySQL and MariaDB databases | | In MySQL and MariaDB, the REGEXP_SUBSTR() function is used to extract substrings from a string based on a specified regular expression pattern. It is thus possible to search for patterns within a character string and retrieve the matching sub-character strings. The function can perform pattern-based substring extractions and can be useful for tasks such as extracting specific parts of a string, parsing text data, and manipulating data based on pattern matching. | | 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 |
| | RLIKE | String [NOT] RLIKE Pattern | | More about RLIKE Function |
|
|
| | | More information about the REGEXP_SUBSTR SQL function: and |
|
|
|
|
