All Products
Search

This Product

    MaxCompute:REGEXP_SUBSTR

    Document Center

    MaxCompute:REGEXP_SUBSTR

    Last Updated:Jul 17, 2023

    Returns a string that matches a given pattern at the nth occurrence specified by occurrence, in the source string from the start position specified by start_position.

    Syntax

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

    Parameters

    • source: required. A value of the STRING type. This parameter specifies the string in which the substring you want to search for.

    • pattern: required. A constant of the STRING type or a regular expression. This parameter specifies the pattern that a specified substring must match. For more information about regular expressions, see Regular expressions.

    • start_position: optional. A constant of the BIGINT type. The value of this parameter must be greater than 0. If you do not specify this parameter, the default value is 1, which indicates that the search starts from the first character of the source string.

    • occurrence: optional. A constant of the BIGINT type. The value of this parameter must be greater than 0. If you do not specify this parameter, the default value is 1, which indicates that the first matched substring is returned.

    Return value

    A value of the STRING type is returned. The return value varies based on the following rules:

    • If pattern is an empty string, an error is returned.

    • If no substring matches the specified pattern, null is returned.

    • If the value of start_position or occurrence is not of the BIGINT type or is less than or equal to 0, an error is returned.

    • If the value of source, pattern, start_position, occurrence, or return_option is null, null is returned.

    Examples

    • Example 1: Return the substring in the I love aliyun very much string that matches a specified pattern. Sample statement: 

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

    • Example 2: An input parameter is set to null. Sample statement: 

      -- The return value is null. 
select regexp_substr('I love aliyun very much', null);

    Related functions

    REGEXP_SUBSTR is a string function. For more information about functions related to string searches and conversion, see String functions.