REGEXP_SUBSTR

Syntax

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

Purpose

Searches for a substring in str that matches the regular expression pattern. If no such substring exists, it returns NULL. This function supports multi-byte characters. If any parameter other than match_param is NULL, the result is NULL. The parameters are described as follows:

• str: the string to search for, which supports multi-byte characters.

• pattern: the regular expression, which follows the rules of MySQL databases.

• position: the starting position for the search. Must be a positive integer greater than 0. If it is less than or equal to 0, an error is returned. If it is NULL, NULL is returned. The default value is 1, indicating that the search starts from the first character.

• occurrence: the number of the match to return. Must be a positive integer greater than or equal to 0. If it is less than or equal to 0, an error is returned. If it is NULL, NULL is returned. The default value is 1, indicating that the first match is returned.

• match_param: the matching rule, a string. For more information about the matching rule, see REGEXP_LIKE.

• subexpr: the number of the group in the regular expression to return. Must be an integer greater than or equal to 0. If it is less than 0, an error is returned. The default value is 0, indicating that the substring matching the entire 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