REGEXP_SUBSTR
Returns the substring that matches a regular expression.
MySQL 8.1
REGEXP_SUBSTR(expr, pattern[, pos[, occurrence[, 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, 1 by defaultINTOptional
- occurrence
- Match number, 1 (the first match) by defaultINTOptional
- match_type
- Match flags, can be combinedVARCHAROptionalcimnu
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.