REGEXP_SUBSTR

Syntax

REGEXP_SUBSTR(str,pattern,[position[,occurrence[,match_param[,subexpr]]]])

Purpose

REGEXP_SUBSTR() searches the str string for the substring that matches the regular expression specified by pattern. The function returns NULL if the substring does not exist. This function supports multi-byte characters. NULL is returned if any parameter other than match_param is set to NULL. The parameters are described as follows:

• str: the string to be searched. Multi-byte characters are supported.

• pattern: a regular expression. The regular expression rules are compatible with the MySQL database.

• position: the start position for the search. The value must be a positive integer, and an error is returned if the value is less than or equal to 0. NULL is returned if NULL is entered. The default value is 1, which specifies to search from the first character.

• occurrence: the ordinal number of occurrences at which the matched value is to be returned. The value must be a positive integer, and an error is returned if the value is less than or equal to 0. NULL is returned if NULL is entered. The default value is 1, which specifies to return the first matched result.

• match_param: the matching rule in the string type. For information about matching rules, see REGEXP_LIKE.

• subexpr: the ordinal number of the group in the regular expression whose value is to be returned. The value is an integer greater than or equal to 0. An error is returned if the value is less than 0. The default value is 0, which means to return the substring that matches the regular expression specified for pattern.

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