SUBSTRING_INDEX-MaxCompute(MaxCompute)-阿里云帮助中心

This function returns the substring from str that is before the countth occurrence of a separator. If count is positive, the function searches from the left. If count is negative, it searches from the right. This is a MaxCompute V2.0 extension function.

Command format

string substring_index(string <str>, string <separator>, int <count>)

Parameters

str: Required. A STRING. The source string.
separator: Required. A STRING. The separator.
count: Required. An INT. The number of occurrences of the separator.

Return value

Returns a STRING value. If any input parameter is NULL, NULL is returned.

Examples

Example 1: Extract a substring from the string https://help.aliyun.com.

--Returns https://help.aliyun.
select substring_index('https://help.aliyun.com', '.', 2);
--Returns aliyun.com.
select substring_index('https://help.aliyun.com', '.', -2);

Example 2: An input parameter is NULL.

--Returns NULL.
select substring_index('https://help.aliyun.com', null, 2);

Related functions

SUBSTRING_INDEX is a string function. For more information about functions that find strings and convert string formats, see String functions.