SPLIT_PART - MaxCompute - Alibaba Cloud Documentation Center

Uses the delimiter specified by separator to split the string specified by str, and returns a substring that starts from the character specified by start and ends with the character specified by end.

Syntax

string split_part(string <str>, string <separator>, bigint <start>[, bigint <end>])

Parameters

str: required. A value of the STRING type. This parameter specifies the string that you want to split. If the input value is of the BIGINT, DOUBLE, DECIMAL, or DATETIME type, the value is implicitly converted into a value of the STRING type before calculation.
separator: required. A constant of the STRING type. This parameter specifies the delimiter that is used to split the string. The delimiter can be a character or a string.
start: required. A constant of the BIGINT type. The value of this parameter must be greater than 0. This parameter specifies the start position of the substring to be returned. The position starts from 1.
end: a constant of the BIGINT type. The value of this parameter must be greater than or equal to the value of start. This parameter specifies the end position of the substring to be returned. If this parameter is not specified, the value of this parameter is equal to the value of start, and the substring that starts from the character specified by start is returned.

Return value

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

If start is set to a value greater than the number of substrings, for example, the string has 6 substrings but the start value is greater than 6, an empty string is returned.
If separator is absent from a string specified by str and start is set to 1, the entire string specified by str is returned. If str is an empty string, an empty string is returned.
If separator is an empty string, the original string specified by str is returned.
If the value of end is greater than the number of substrings, the substring that starts from the character specified by start is returned.
If the value of str is not of the STRING, BIGINT, DOUBLE, DECIMAL, or DATETIME type, an error is returned.
If the value of separator is not a constant of the STRING type, an error is returned.
If the value of start or end is not a constant of the BIGINT type, an error is returned.
If the value of an input parameter except separator is null, null is returned.

Examples

Example 1: Use a comma (,) as a delimiter to split the string a,b,c,d and return the substring that matches the specified rule. Sample statement:
```
-- The return value is a. 
select split_part('a,b,c,d', ',', 1);
-- The return value is a,b. 
select split_part('a,b,c,d', ',', 1, 2);
```
Example 2: The value of start is greater than the number of substrings after the specified string is split into the substrings. Sample statement:
```
-- The return value is an empty string. 
select split_part('a,b,c,d', ',', 10);
```

Example 3: separator does not exist in the string specified by str. Sample statement:

-- The return value is a,b,c,d. 
select split_part('a,b,c,d', ':', 1);
-- The return value is an empty string. 
select split_part('a,b,c,d', ':', 2);

Example 4: separator is an empty string. Sample statement:

-- The return value is a,b,c,d. 
select split_part('a,b,c,d', '', 1);

Example 5: The value of end is greater than the number of substrings after the specified string is split into the substrings. Sample statement:
```
-- The return value is b,c,d. 
select split_part('a,b,c,d', ',', 2, 6);
```
Example 6: An input parameter except separator is set to null. Sample statement:
```
-- The return value is null. 
select split_part('a,b,c,d', ',', null);
```

Related functions

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