All Products
Search

This Product

    MaxCompute:SUBSTR

    Document Center

    MaxCompute:SUBSTR

    Last Updated:Jul 17, 2023

    Returns a substring that starts from start_position in a string specified by str and has a length specified by length.

    Syntax

    string substr(string <str>, bigint <start_position>[, bigint <length>])

    Parameters

    • str: required. A value of the STRING type. If the input value is of the BIGINT, DECIMAL, DOUBLE, or DATETIME type, it is implicitly converted into a value of the STRING type before calculation.

    • start_position: required. A value of the BIGINT type. Default value: 1.

      • Hive-compatible data type edition: If start_position is set to 0, the return value is the same as that when this parameter is set to 1.

      • MaxCompute V1.0 and MaxCompute V2.0 data type editions: If start_position is set to 0, null is returned.

    • length: optional. A value of the BIGINT type. This parameter specifies the length of a substring. The value of this parameter must be greater than 0.

      Important

      • If setproject odps.function.strictmode is set to false and the value of the length parameter is less than 0, no substring is returned.

      • If setproject odps.function.strictmode is set to true and the value of the length parameter is less than 0, an error is returned.

    Return value

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

    • If the value of str is not of the STRING, BIGINT, DECIMAL, DOUBLE, or DATETIME type, an error is returned.

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

    • If length is not specified, the substring from the start position to the end of str is returned.

    • If the value of str, start_position, or length is null, null is returned.

    Examples

    • Example 1: Return a substring with the specified length that starts from the specified position in the string abc. Sample statement: 

      -- The return value is bc. 
select substr('abc', 2);
-- The return value is b. 
select substr('abc', 2, 1);
-- The return value is bc. 
select substr('abc',-2 , 2);

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

      -- The return value is null. 
select substr('abc', null);

    Related functions

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