Categories:

String & binary functions (General)

SPLIT_PART¶

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

To return all characters after a specified character, you can use the POSITION and SUBSTR functions. For an example, see Returning substrings for email, phone, and date strings.

Tip

You can use the search optimization service to improve the performance of queries that call this function. For details, see Search Optimization Service.

See also:

SPLIT

Syntax¶

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

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.

Returns¶

This function returns a value of type VARCHAR.

If any argument is NULL, NULL is returned.

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¶

The collation specifications of all input arguments must be compatible.

This function does not support the following collation specifications:

  • pi (punctuation-insensitive).

  • cs-ai (case-sensitive, accent-insensitive).

Examples¶

This example shows the portions returned by different partNumber values.

SELECT column1 part_number_value, column2 portion
  FROM VALUES
    (0, SPLIT_PART('11.22.33', '.',  0)),
    (1, SPLIT_PART('11.22.33', '.',  1)),
    (2, SPLIT_PART('11.22.33', '.',  2)),
    (3, SPLIT_PART('11.22.33', '.',  3)),
    (4, SPLIT_PART('11.22.33', '.',  4)),
    (-1, SPLIT_PART('11.22.33', '.',  -1)),
    (-2, SPLIT_PART('11.22.33', '.',  -2)),
    (-3, SPLIT_PART('11.22.33', '.',  -3)),
    (-4, SPLIT_PART('11.22.33', '.',  -4));
Copy
+-------------------+---------+
| PART_NUMBER_VALUE | PORTION |
|-------------------+---------|
|                 0 | 11      |
|                 1 | 11      |
|                 2 | 22      |
|                 3 | 33      |
|                 4 |         |
|                -1 | 33      |
|                -2 | 22      |
|                -3 | 11      |
|                -4 |         |
+-------------------+---------+

This example returns the first and last parts of the localhost IP address 127.0.0.1:

SELECT SPLIT_PART('127.0.0.1', '.', 1) AS first_part,
       SPLIT_PART('127.0.0.1', '.', -1) AS last_part;
Copy
+------------+-----------+
| FIRST_PART | LAST_PART |
|------------+-----------|
| 127        | 1         |
+------------+-----------+

This example returns the first and second parts of a string of characters that are separated by vertical bars. The separator is the first part of the input string, so the first element after the split is an empty string.

SELECT SPLIT_PART('|a|b|c|', '|', 1) AS first_part,
       SPLIT_PART('|a|b|c|', '|', 2) AS last_part;
Copy
+------------+-----------+
| FIRST_PART | LAST_PART |
|------------+-----------|
|            | a         |
+------------+-----------+

This example shows a multi-character separator:

SELECT SPLIT_PART('aaa--bbb-BBB--ccc', '--', 2) AS multi_character_separator;
Copy
+---------------------------+
| MULTI_CHARACTER_SEPARATOR |
|---------------------------|
| 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 column1 part_number_value, column2 portion
  FROM VALUES
    (1, split_part('user@snowflake.com', '',  1)),
    (-1, split_part('user@snowflake.com', '', -1)),
    (2, split_part('user@snowflake.com', '',  2)),
    (-2, split_part('user@snowflake.com', '', -2));
Copy
+-------------------+--------------------+
| PART_NUMBER_VALUE | PORTION            |
|-------------------+--------------------|
|                 1 | user@snowflake.com |
|                -1 | user@snowflake.com |
|                 2 |                    |
|                -2 |                    |
+-------------------+--------------------+
Language: English