REGEXP_SUBSTR command format, parameters, and return value-MaxCompute(MaxCompute)-阿里云帮助中心

Returns the substring in source that is the occurrence-th match for the specified pattern, starting at start_position.

Command format

string regexp_substr(string <source>, string <pattern>[, bigint <start_position>[, bigint <occurrence>]])

Parameters

source: Required. A value of the STRING type. The string to search.
pattern: Required. A constant of the STRING type or a regular expression. The pattern to match. For more information about how to write regular expressions, see Regular expression rules.
start_position: Optional. A BIGINT constant that must be greater than 0. If you do not specify this parameter, the default value is 1. This means the search starts from the first character of source.
occurrence: Optional. A BIGINT constant that must be greater than 0. If you do not specify this parameter, the default value is 1. This means the function returns the first matched substring.

Return value

Returns a value of the STRING type. The following rules apply:

If pattern is an empty string, an error is returned.
If no match is found, NULL is returned.
If start_position or occurrence is not a BIGINT value or is less than or equal to 0, an error is returned.
If source, pattern, start_position, occurrence, or return_option is NULL, NULL is returned.

Examples

Example 1: Return a substring from the string I love aliyun very much that matches a specified pattern. Sample command:

-- Returns aliyun.
select regexp_substr('I love aliyun very much', 'a[[:alpha:]]{5}');
-- Returns have.
select regexp_substr('I have 2 apples and 100 bucks!', '[[:blank:]][[:alnum:]]*', 1, 1);
-- Returns 2.
select regexp_substr('I have 2 apples and 100 bucks!', '[[:blank:]][[:alnum:]]*', 1, 2);

Example 2: An input parameter is NULL. Sample command:

-- Returns NULL.
select regexp_substr('I love aliyun very much', null);

Related functions

REGEXP_SUBSTR is a string function. For more information about functions for finding strings and converting string formats, see String functions.