Топ-100
 

REGEXP_SUBSTR

RDBMS Type: MySQL
Topic

REGEXP_SUBSTR(expr, pat[, pos[, occurrence[, match_type]]])

Returns the substring of the string expr that matches the regular
expression specified by the pattern pat, NULL if there is no match. If
expr or pat is NULL, the return value is NULL.

REGEXP_SUBSTR() takes these optional arguments:

o pos: The position in expr at which to start the search. If omitted,
the default is 1.

o occurrence: Which occurrence of a match to search for. If omitted,
the default is 1.

o match_type: A string that specifies how to perform matching. The
meaning is as described for REGEXP_LIKE().

Prior to MySQL 8.0.17, the result returned by this function used the
UTF-16 character set; in MySQL 8.0.17 and later, the character set and
collation of the expression searched for matches is used. (Bug #94203,
Bug #29308212)

For additional information about how matching occurs, see the
description for REGEXP_LIKE().

URL: https://dev.mysql.com/doc/refman/8.0/en/regexp.html

Example

mysql> SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+');
+----------------------------------------+
| REGEXP_SUBSTR('abc def ghi', '[a-z]+') |
+----------------------------------------+
| abc |
+----------------------------------------+
mysql> SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3);
+----------------------------------------------+
| REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3) |
+----------------------------------------------+
| ghi |
+----------------------------------------------+