REGEXP_SUBSTR
Returns the substring that matches a regular expression.
MySQL 8.1
REGEXP_SUBSTR(expr, pattern[, pos[, occurrence[, 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, 1 by default
- occurrenceINToptional
- Match number, 1 (the first match) by default
- match_typeVARCHARoptional
- Match flags as in REGEXP_LIKE, for example c for case-sensitive matching
Return value
The matching substring; NULL if there is no match or if expr or pattern is NULL
Examples
Date from text
MySQL 8.1
SELECT REGEXP_SUBSTR(
'Order from 2024-03-08',
'[0-9]{4}-[0-9]{2}-[0-9]{2}'
);Third number in a string
MySQL 8.1
SELECT REGEXP_SUBSTR('a1b22c333', '[0-9]+', 1, 3);Street from an address
MySQL 8.1
SELECT address,
REGEXP_SUBSTR(address, '[^, ][^,]*', 1, 2) AS street
FROM Rooms
LIMIT 5;Details
Groups in parentheses are not returned separately: the result is the whole match. To get a part of the match, narrow the pattern or use REGEXP_REPLACE with $1. pos must be from 1 to the string length + 1: with 0 or a larger value the function gives an error.