©SQL :: Manager
HomeProgram InformationTechnical detailsFree downloadDonate for a cup of coffee
myway SQL Manager for MySQL and MariaDB, Download on Uptodown
SQL functionMySQLMariaDB

The REGEXP_INSTR SQL Function in MySQL and MariaDB - Starting index of regular expression

REGEXP_INSTRSyntax:REGEXP_INSTR(String, Pattern [, Position] [, Occurrence] [, Return option] [, Match type])
Return value:INTEGER
Function type:Regular Expressions
Function Description

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:


TypeDescription
cCase sensitive.
iCase-insensitive match.
mMultiline mode. Line terminators within the string are recognized. The default behavior is to match line terminators only at the beginning and end of "String".
nA point "." is interpreted as a line terminator: Default.
uUnix-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: Download

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.
RANDRANDOM_BYTESREGEXPREGEXP_INSTRREGEXP_LIKEREGEXP_REPLACEREGEXP_SUBSTR

Further MySQL und MariaDB SQL Regular Expressions

REGEXPString [NOT] REGEXP Pattern
More about REGEXP Function

REGEXP_LIKEREGEXP_LIKE(String, Pattern [, Match type])
More about REGEXP_LIKE Function

REGEXP_REPLACEREGEXP_REPLACE(String, Pattern, Replace by [, Position] [, Occurrence] [, Match type])
More about REGEXP_REPLACE Function

REGEXP_SUBSTRREGEXP_SUBSTR(String, Pattern [, Position] [, Occurrence] [, Match type])
More about REGEXP_SUBSTR Function

RLIKEString [NOT] RLIKE Pattern
More about RLIKE Function

Numeric functionsString functionsRegular ExpressionsDate and Time functions
Comparison functionsEncryption & CompressionConversion functionsNULL functions
Aggregate functionsWindow functionsJSON functionsGeometric functions
Sequence functionsInformation functionsDynamic ColumnsMiscellaneous functions
More information about the REGEXP_INSTR SQL function: mysql.com and mariadb.com
Updated: 2023-09-24ImprintContactTerms & conditionsPrivacy & CookiesUpdatesSitemapFacebookLinkedinTwitterStatistics©2020-2024 Heino Cunze-Fischer