Splits a string on a delimiter and returns one or more consecutive segments by position.
Syntax
string split_part(string <str>, string <separator>, bigint <start>[, bigint <end>])Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
str | STRING | Yes | The string to split. BIGINT, DOUBLE, DECIMAL, and DATETIME values are implicitly converted to STRING before processing. |
separator | STRING constant | Yes | The delimiter to split on. Can be a single character or a multi-character string. |
start | BIGINT constant | Yes | The 1-based index of the first segment to return. Must be greater than 0. |
end | BIGINT constant | No | The 1-based index of the last segment to return. Must be greater than or equal to start. If omitted, defaults to start and returns a single segment. |
Return value
Returns a STRING value. The following rules apply:
| Condition | Return value |
|---|---|
start exceeds the number of segments | Empty string |
separator is not found in str and start is 1 | The original str |
separator is not found in str and start is greater than 1 | Empty string |
separator is an empty string | The original str |
end exceeds the number of segments | Segments from start to the last segment |
Any parameter except separator is null | null |
str is not STRING, BIGINT, DOUBLE, DECIMAL, or DATETIME | Error |
separator is not a STRING constant | Error |
start or end is not a BIGINT constant | Error |
start is 1-based and must be greater than 0. Passing 0 returns an error.Examples
Extract a segment by index
Use a comma as the delimiter and return segments from a,b,c,d by position:
-- Returns: a
SELECT split_part('a,b,c,d', ',', 1);
-- Returns: a,b
SELECT split_part('a,b,c,d', ',', 1, 2);Return value when index exceeds number of segments
When start is greater than the total number of segments, an empty string is returned:
-- 'a,b,c,d' has 4 segments; start=10 exceeds that count.
-- Returns: empty string
SELECT split_part('a,b,c,d', ',', 10);Return value when the delimiter is absent
When separator is not found in str, the entire string is returned for start=1, and an empty string for any higher index:
-- ':' is not in 'a,b,c,d'; start=1 returns the full string.
-- Returns: a,b,c,d
SELECT split_part('a,b,c,d', ':', 1);
-- start=2 returns an empty string.
-- Returns: empty string
SELECT split_part('a,b,c,d', ':', 2);Return value when the delimiter is an empty string
When separator is an empty string, the original string is returned unchanged:
-- Returns: a,b,c,d
SELECT split_part('a,b,c,d', '', 1);Return value when end exceeds number of segments
When end is greater than the total number of segments, all segments from start to the last segment are returned:
-- 'a,b,c,d' has 4 segments; end=6 clamps to the last segment.
-- Returns: b,c,d
SELECT split_part('a,b,c,d', ',', 2, 6);Return value when a parameter is null
When any parameter except separator is null, null is returned:
-- Returns: null
SELECT split_part('a,b,c,d', ',', null);Related functions
SPLIT_PART is a string function. For more information about string search and conversion functions, see String functions.