The REGEXP_INSTR SQL Function in MySQL and MariaDB - Starting index of regular expression |
|
| REGEXP_INSTR | Syntax: | REGEXP_INSTR(String, Pattern [, Position] [, Occurrence] [, Return option] [, Match type]) | Return value: | INTEGER | Function type: | Regular Expressions | |
| | The REGEXP_INSTR() function returns the starting index of the substring of "String" that matches the regular expression "Pattern".
The function returns 0 if there is no match. Character indices start at 1.
If the value of "String" or "Pattern" is NULL, the return value of the REGEXP_INSTR() 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.
"Return option": What kind of position should be returned. If this value is 0, REGEXP_INSTR() returns the position of the first character of the matched substring. If this value is 1, REGEXP_INSTR() returns the position after the matched substring. The default is 0 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_INSTR() function on MariaDB 10.3 |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
|
select regexp_instr('Test-aa', 'a');
select regexp_instr(binary 'Test-aa', 'a');
select regexp_instr('Test-aa', 'x');
select regexp_instr('Test-aa', 'T');
SELECT REGEXP_INSTR('Test-aa-abc', 'AB');
SELECT REGEXP_INSTR('Test-aa-abc', '(?i)AB');
SELECT REGEXP_INSTR('Test-aa-abc', '(?i)T(?-i)est');
SELECT REGEXP_INSTR('Test-aa-abc', '(?i)T(?-i)EST');
SELECT REGEXP_INSTR(null, 'abc');
SELECT REGEXP_INSTR('Test-aa-abc', null);
|
|
regexp_instr('Test-aa', 'a') |
int(11) |
6 |
|
|
regexp_instr(binary 'Test-aa', 'a') |
int(11) |
6 |
|
|
regexp_instr('Test-aa', 'x') |
int(11) |
0 |
|
|
regexp_instr('Test-aa', 'T') |
int(11) |
1 |
|
|
REGEXP_INSTR('Test-aa-abc', 'AB') |
int(11) |
0 |
|
|
REGEXP_INSTR('Test-aa-abc', '(?i)AB') |
int(11) |
9 |
|
|
REGEXP_INSTR('Test-aa-abc', '(?i)T(?-i)est') |
int(11) |
1 |
|
|
REGEXP_INSTR('Test-aa-abc', '(?i)T(?-i)EST') |
int(11) |
0 |
|
|
REGEXP_INSTR(null, 'abc') |
int(11) |
NULL |
|
|
REGEXP_INSTR('Test-aa-abc', null) |
int(11) |
NULL |
|
|
| Examples of the REGEXP_INSTR() function on MySQL 8.0 |
1
2
3
4
5
6
7
8
9
10
11
12
13
|
|
SELECT REGEXP_INSTR('Test-abc-abc-abc', 'ab');
SELECT REGEXP_INSTR('Test-abc-abc-abc', 'ab', 7);
SELECT REGEXP_INSTR('Test-abc-abc-abc', 'ab', 1, 3);
SELECT REGEXP_INSTR('Test-abc-abc-abc', 'ab', 1, 3, 0);
SELECT REGEXP_INSTR('Test-abc-abc-abc', 'ab', 1, 3, 1);
SELECT REGEXP_INSTR('Test-abc-abc-abc', 'AB', 1, 3, 0);
SELECT REGEXP_INSTR('Test-abc-abc-abc', 'AB', 1, 3, 0, 'i');
SELECT REGEXP_INSTR(null, 'abc');
SELECT REGEXP_INSTR('Test-aa-abc', null);
|
|
REGEXP_INSTR('Test-abc-abc-abc', 'ab') |
bigint(21) |
6 |
|
|
REGEXP_INSTR('Test-abc-abc-abc', 'ab', 7) |
bigint(21) |
10 |
|
|
REGEXP_INSTR('Test-abc-abc-abc', 'ab', 1, 3) |
bigint(21) |
14 |
|
|
REGEXP_INSTR('Test-abc-abc-abc', 'ab', 1, 3, 0) |
bigint(21) |
14 |
|
|
REGEXP_INSTR('Test-abc-abc-abc', 'ab', 1, 3, 1) |
bigint(21) |
16 |
|
|
REGEXP_INSTR('Test-abc-abc-abc', 'AB', 1, 3, 0) |
bigint(21) |
0 |
|
|
REGEXP_INSTR('Test-abc-abc-abc', 'AB', 1, 3, 0, 'i') |
bigint(21) |
14 |
|
|
REGEXP_INSTR(null, 'abc') |
bigint(21) |
NULL |
|
|
REGEXP_INSTR('Test-aa-abc', null) |
bigint(21) |
NULL |
|
|
| The examples were created with the MyWAY SQL manager: | How to use the REGEXP_INSTR() function in MySQL and MariaDB databases | The REGEXP_INSTR() function in MySQL and MariaDB returns the position of the first occurrence of a regular expression pattern within a string. The function returns the position as an integer value. REGEXP_INSTR() provides a convenient way of determining the position of a regular expression match within a string. It can be used for various purposes, such as finding specific patterns, extracting substrings, or performing conditional operations based on the position of a match.
In MySQL there are additional options for this function that are not available in MariaDB. | | Further MySQL und MariaDB SQL Regular Expressions | REGEXP | String [NOT] REGEXP Pattern | More about REGEXP 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_INSTR SQL function: and |
|
|
|
|