Skip to main content

REGEXP_SUBSTR

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.

Syntax

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

Arguments

ArgumentsDescription
exprThe string expr that to be matched
patThe regular expression
posOptional. The position in expr at which to start the search. If omitted, the default is 1.
occurrenceOptional. Which occurrence of a match to search for. If omitted, the default is 1.
match_typeOptional. A string that specifies how to perform matching. The meaning is as described for REGEXP_LIKE().

Return Type

VARCHAR

Examples

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

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

SELECT REGEXP_SUBSTR('周 周周 周周周 周周周周', '周+', 2, 3);
+------------------------------------------------------------------+
| REGEXP_SUBSTR('周 周周 周周周 周周周周', '周+', 2, 3) |
+------------------------------------------------------------------+
| 周周周周 |
+------------------------------------------------------------------+