Categories:

String & Binary Functions (General)

SPLIT_PART

Splits a given string at a specified character and returns the requested part.

If any parameter is NULL, NULL is returned.

See also

SPLIT

Syntax

SPLIT_PART(<string>, <delimiter>, <partNumber>)

Arguments

string

Text to be split into parts.

delimiter

Text representing the delimiter to split by.

partNumber

Requested part of the split (1-based).

If the value is negative, the parts are counted backward from the end of the string.

Usage Notes

  • If the partNumber is out of range, the returned value is an empty string.

  • If the string starts or is terminated with the delimiter, the system considers empty space before or after the delimiter, respectively, as a valid part of the split result. For an example, see the Examples section below.

  • If the partNumber is 0, it is treated as 1. In other words, it gets the first element of the split. To avoid confusion over whether indexes are 1-based or 0-based, Snowflake recommends avoiding the use of 0 as a synonym for 1.

  • If the separator is an empty string, then after the split, the returned value is the input string (the string is not split).

Collation Details

Arguments with collation specifications are currently not supported.

Examples

Demonstrate the portions returned by different partNumber values.

select  0, split_part('11.22.33', '.',  0) UNION
select  1, split_part('11.22.33', '.',  1) UNION
select  2, split_part('11.22.33', '.',  2) UNION
select  3, split_part('11.22.33', '.',  3) UNION
select  4, split_part('11.22.33', '.',  4) UNION
select -1, split_part('11.22.33', '.', -1) UNION
select -2, split_part('11.22.33', '.', -2) UNION
select -3, split_part('11.22.33', '.', -3) UNION
select -4, split_part('11.22.33', '.', -4)
;
+----+---------------------------------+
|  0 | SPLIT_PART('11.22.33', '.',  0) |
|----+---------------------------------|
|  0 | 11                              |
|  1 | 11                              |
|  2 | 22                              |
|  3 | 33                              |
|  4 |                                 |
| -1 | 33                              |
| -2 | 22                              |
| -3 | 11                              |
| -4 |                                 |
+----+---------------------------------+

Return the first and last parts of the localhost IP address 127.0.0.1:

SELECT SPLIT_PART('127.0.0.1', '.', 1), SPLIT_PART('127.0.0.1', '.', -1);
+---------------------------------+----------------------------------+
| SPLIT_PART('127.0.0.1', '.', 1) | SPLIT_PART('127.0.0.1', '.', -1) |
|---------------------------------+----------------------------------|
| 127                             | 1                                |
+---------------------------------+----------------------------------+

Return the first and second parts of a string of characters that are separated by vertical bars. Note that the separator is the first part of the input string, and therefore the first element after the split is an empty string.

SELECT SPLIT_PART('|a|b|c|', '|', 1), SPLIT_PART('|a|b|c|', '|', 2);
+-------------------------------+-------------------------------+
| SPLIT_PART('|A|B|C|', '|', 1) | SPLIT_PART('|A|B|C|', '|', 2) |
|-------------------------------+-------------------------------|
|                               | a                             |
+-------------------------------+-------------------------------+

The following example shows a multi-character separator:

SELECT SPLIT_PART('aaa--bbb-BBB--ccc', '--', 2);
+------------------------------------------+
| SPLIT_PART('AAA--BBB-BBB--CCC', '--', 2) |
|------------------------------------------|
| bbb-BBB                                  |
+------------------------------------------+

The following example shows that if the separator is an empty string, then after the split, there is still only one string:

select  1, split_part('user@snowflake.net', '',  1) UNION
select -1, split_part('user@snowflake.net', '', -1) UNION
select  2, split_part('user@snowflake.net', '',  2) UNION
select -2, split_part('user@snowflake.net', '', -2);
+----+------------------------------------------+
|  1 | SPLIT_PART('USER@SNOWFLAKE.NET', '',  1) |
|----+------------------------------------------|
|  1 | user@snowflake.net                       |
| -1 | user@snowflake.net                       |
|  2 |                                          |
| -2 |                                          |
+----+------------------------------------------+