SPLIT_PART

split_part(str, delim, partNum)

Description

The SPLIT_PART function is used to split a string (str) by a specified delimiter (delim) and return the substring at the specified position (partNum).

Parameter Description

• str (string): The string to be split.
• delim (string): The string used as a delimiter to split the input string into multiple substrings.
• partNum (bigint): Specifies the position of the substring to return. When partNum is greater than 0, it indicates the partNum-th substring from the left; when partNum is less than 0, it indicates the partNum-th substring from the right. If partNum exceeds the number of substrings after splitting, an empty string is returned. partNum cannot be 0.

Return Result

Returns a string (string) representing the substring at the specified position.

Usage Example

-- Example 1: Return the second substring
SELECT split_part('a,b,c', ',', 2);
-- Result: b

-- Example 2: Return the last substring
SELECT split_part('a,b,c', ',', -1);
-- Result: c

-- Example 3: Return the third substring (counting from the right)
SELECT split_part('a,b,c,d,e', ',', -2);
-- Result: d

-- Example 4: When partNum exceeds the number of substrings, return an empty string
SELECT split_part('a,b,c', ',', 4);
-- Result is empty

-- Example 5: Use multiple characters as delimiters
SELECT split_part('apple-orange-grape', '-|g', 1);
-- Result: apple-orange-grape

Notes

• When partNum is 0, the function will return an error.
• If the input string or delimiter is empty, the function will return an empty string.
• When partNum is negative, the position of the substring is calculated from the right side.
• If multiple characters are needed as delimiters, multiple characters can be used consecutively or a regular expression can be used for splitting.