DocsICP FilingConsole
Documentation
Home Page MaxCompute Developer Reference SQL developer reference Function reference Built-in functions String Functions SPLIT_PART

SPLIT_PART

Updated at:
Copy as MD
Product
My Favorites

Splits source_string based on the specified delimiter and returns the substring specified by start_position and the optional end_position.

Syntax

STRING SPLIT_PART(STRING <source_string>, STRING <delimiter>, BIGINT <start_position>[, BIGINT <end_position>])

-- Standard example.
-- Returns a.
SELECT SPLIT_PART('a,b,c,d', ',', 1);

Parameters

  • source_string — STRING, required. The source string to split. BIGINT, DOUBLE, DECIMAL, and DATETIME values are implicitly converted to STRING before processing.

  • delimiter — STRING constant, required. The delimiter used to split source_string. The value can be a single character or a multi-character string.

  • start_position — BIGINT constant, required. The index of the first returned segment, starting from 1. The value must be greater than 0.

  • end_position — BIGINT constant, optional. The index of the last returned segment. The value must be greater than or equal to start_position. If omitted, end_position takes the same value as start_position and returns only the segment that start_position specifies.

Return value

Returns a STRING value. The following rules apply:

  • Index out of range

    • If start_position is greater than the number of segments after splitting, an empty string is returned.

    • If end_position is greater than the number of segments after splitting, all segments from start_position to the end of the string are returned.

  • Delimiter does not exist

    • If delimiter does not exist in source_string and start_position is 1, the entire source_string is returned.

    • If source_string is an empty string, an empty string is returned.

  • Empty delimiter

    • The original source_string is returned directly.

  • Type error

    • An error is returned if source_string is not of the STRING, BIGINT, DOUBLE, DECIMAL, or DATETIME type.

    • An error is returned if delimiter is not a STRING constant.

    • An error is returned if start_position or end_position is not a BIGINT constant.

  • NULL return value

    • NULL is returned if any parameter, including delimiter, is NULL.

Examples

  • Extract segments by index: use the delimiter , to split the string a,b,c,d and return the specified segments.

    -- Returns a.
SELECT SPLIT_PART('a,b,c,d', ',', 1);
-- Returns a,b.
SELECT SPLIT_PART('a,b,c,d', ',', 1, 2);

  • Index out of range: the value of start_position is greater than the actual number of segments after splitting.

    -- Returns an empty string.
SELECT SPLIT_PART('a,b,c,d', ',', 10);

  • Delimiter does not exist: delimiter does not exist in source_string.

    -- Returns a,b,c,d.
SELECT SPLIT_PART('a,b,c,d', ':', 1);
-- Returns an empty string.
SELECT SPLIT_PART('a,b,c,d', ':', 2);

  • Index out of range: the value of end_position is greater than the actual number of segments after splitting.

    -- Returns b,c,d.
SELECT SPLIT_PART('a,b,c,d', ',', 2, 6);

  • Extract the username from an email address.

    -- Returns alicexxx.
SELECT SPLIT_PART('alicexxx@example.com', '@', 1) AS username;

  • Extract a field by using a multi-character delimiter.

    -- Returns banana.
SELECT SPLIT_PART('apple||banana||cherry', '||', 2) AS second_item;

  • NULL return value: NULL is returned if any parameter, including delimiter, is NULL.

    -- All expressions return NULL.
SELECT
  SPLIT_PART(NULL, ',', 1) AS null_string,
  SPLIT_PART('a,b,c', NULL, 1) AS null_delimiter,
  SPLIT_PART('a,b,c', ',', NULL) AS null_part_num;
Previous：SPLITNext：SUBSTR