REGEXP_INSTR
Returns the position of the substring that matches a regular expression.
MySQL 8.4
REGEXP_INSTR(expr, pattern[, pos[, occurrence[, return_option[, match_type]]]])- [ ]optional part
- expr
- String to search inVARCHAR
- pattern
- Regular expressionVARCHAR
- pos
- Position to start the search from, from 1 to the string length, 1 by defaultINTOptional
- occurrence
- Match number, 1 (the first match) by defaultINTOptional
- return_option
- 0 for the start of the match, 1 for the position after it; 0 by defaultINTOptional01
- match_type
- Match flags, can be combinedVARCHAROptionalcimnu
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.4
SELECT REGEXP_INSTR('sql-academy-2024', '[0-9]+');Start and end of the second number
MySQL 8.4
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.4
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.