REGEXP_SUBSTR

Syntax

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

Description

Searches for a substring in str that matches the regular expression pattern. If no matching substring is found, 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 specifies the string to search for. It supports multi-byte characters.

• pattern specifies the regular expression. The regular expression rules are compatible with those of MySQL databases.

• position specifies the starting position for the search. It 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 specifies the number of the matching result to return. It 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 matching result is returned.

• match_param specifies the matching rules. It is a string. For more information about the matching rules, see REGEXP_LIKE.

• subexpr specifies the number of the group in the regular expression to return. It 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

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 (0.005 sec)

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 (0.001 sec)