All Products
Search

This Product

    MaxCompute:SUBSTRING

    Document Center

    MaxCompute:SUBSTRING

    Last Updated:Aug 16, 2023

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

    Syntax

    string substring(string|binary <str>, int <start_position>[, int <length>])

    Parameters

    • str: required. A value of the STRING or BINARY type.

    • start_position: required. A value of the INT type. The start position starts from 1. If start_position is set to 0, an empty string is returned. If start_position is set to a negative value, the search starts from the end to the start of the string and the last character is -1.

    • 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.

    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 or BINARY type or cannot be implicitly converted into a value of the STRING or BINARY 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 the string specified by str is returned.

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

    Examples

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

      -- The return value is bc. 
select substring('abc', 2);
-- The return value is b. 
select substring('abc', 2, 1);
-- The return value is bc. 
select substring('abc',-2,2);
-- The return value is ab. 
select substring('abc',-3,2);
-- The return value is 001. 
select substring(bin(2345), 2, 3);

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

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

    Related functions

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