Declaration
REGEXP_SUBSTR(str,pattern,[position[,occurrence[,match_param[,subexpr]]]])
Description
Searches for a substring that matches the regular expression pattern in str. If no such substring exists, returns NULL. This function supports multibyte characters. If any parameter other than match_param is NULL, the result is NULL. The parameters are described as follows:
str: The string to be searched. Multibyte characters are supported.patternspecifies the regular expression. The regular expression rules are compatible with those of MySQL Database.positionindicates the starting position of the search. It must be a positive integer greater than 0. A value less than or equal to 0 returns an error. IfNULLis entered,NULLis returned. The default value is 1, which means the search starts from the first character.occurrence: the position of the first occurrence to return. It must be a non-negative integer. If it is less than or equal to 0, an error is returned. If the value isNULL,NULLis returned. The default value is1, which means the first matched value is returned.match_param: The matching rule, which is a string. For more information about matching rules, see REGEXP_LIKE.subexprspecifies which group in the regular expression to return. If the value is an integer greater than or equal to 0, that group is returned. If the value is negative, an error is returned. The default value is 0, which means the entire substring that matches the pattern is returned.
Examples
obclient> SELECT REGEXP_SUBSTR('I have 2 apples and 100 bucks!', '[[:blank:]][[:alnum:]]*', 1, 1) FROM DUAL;
+----------------------------------------------------------------------------------+
| regexp_substr('I have 2 apples and 100 bucks!', '[[:blank:]][[:alnum:]]*', 1, 1) |
+----------------------------------------------------------------------------------+
| have |
+----------------------------------------------------------------------------------+
1 row in set
obclient> SELECT REGEXP_SUBSTR('foothebar', 'foo(.*)(bar)', 1, 1, 'c', 1) FROM DUAL;
+----------------------------------------------------------+
| regexp_substr('foothebar', 'foo(.*)(bar)', 1, 1, 'c', 1) |
+----------------------------------------------------------+
| the |
+----------------------------------------------------------+
1 row in set