REGEXP_INSTR
Returns the position of the substring that matches a regular expression.
MySQL 8.1
REGEXP_INSTR(expr, pattern[, pos[, occurrence[, return_option[, match_type]]]])Parameters
- exprVARCHAR
- String to search in
- patternVARCHAR
- Regular expression
- posINToptional
- Position to start the search from, from 1 to the string length, 1 by default
- occurrenceINToptional
- Match number, 1 (the first match) by default
- return_optionINToptional
- Start of the match by default, with 1 the position right after it
- Values:01
- match_typeVARCHARoptional
- Match flags as in REGEXP_LIKE, for example c for case-sensitive matching
Return value
An integer position starting from 1; 0 if there is no match; NULL if expr or pattern is NULL
Examples
Position of the first number
MySQL 8.1
SELECT REGEXP_INSTR('sql-academy-2024', '[0-9]+');Start and end of the second number
MySQL 8.1
SELECT REGEXP_INSTR('a1b22c333', '[0-9]+', 1, 2) AS match_start,
REGEXP_INSTR('a1b22c333', '[0-9]+', 1, 2, 1) AS after_match;Address without the postal code
MySQL 8.1
SELECT address,
SUBSTRING(
address,
REGEXP_INSTR(address, '^[0-9-]+, ', 1, 1, 1)
) AS without_zip
FROM Rooms
LIMIT 5;Details
pos must be from 1 to the string length: 0, a negative number or a position past the last character give the error «Index out of bounds in regular expression search», not 0.
With return_option = 1 the function returns the position right after the match, and you can pass it straight to SUBSTRING to take the text after the found fragment.