Snowflake Split String on Delimiter-Functions and Examples

The split string is one of the common requirements in many relational database and programming languages. For example, get username from the string. Many RDBMS provides the functions or methods to split string on a delimiter and extract required text from an array. In this article, we will check Snowflake functions to split string on a delimiter.

Snowflake Split String

In many relational databases such as Netezza, PostgreSQL, etc, you can use array functions to extract records from split string result.

But, Snowflake supports many useful split functions that you can use to extract the required field from a delimited strings.

Snowflake Split String Functions

Following split functions are used to split a string on a delimiter.

SPLIT_PART
SPLIT
STRTOK
SPLIT_TO_TABLE

You can use any of the below mentioned functions as per your requirements.

Now, let us check these functions with an examples.

Snowflake SPLIT_PART Function

The SPLIT_PART function splits a given string on a delimiter and returns the requested part.

If a part does not exist, an empty string is returned. If any parameter is NULL, NULL is returned.

Following is the SPLIT_PART function syntax

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

where, the string is the input string. The delimiter is the string delimiter and partnr is part number to be returned.

For example, consider below SQL statement to split string on comma delimiter and return the first and last parts of the string.

SELECT SPLIT_PART(col1, ',', 1) as firstValue, SPLIT_PART(col1, ',', -1) as lastValue FROM   (SELECT 'aaa,bbb,ccc,ddd,eee' AS col1);

+------------+-----------+
| FIRSTVALUE | LASTVALUE |
|------------+-----------|
| aaa        | eee       |
+------------+-----------+

Note that, -1 will return the last part of the string.

Snowflake SPLIT Function

The SPLIT function splits a given string with a given separator and returns the result in an array of strings.

Following is the SPLIT function syntax

SPLIT(<string>, <separator>)

Where, the string is input string and separator is delimiter.

For example, consider below SQL statement to split string on comma delimiter and return an array of string.

SELECT SPLIT(col1, ',') as str_array
FROM (SELECT 'aaa,bbb,ccc,ddd,eee' AS col1) AS a;

+-----------+
| STR_ARRAY |
|-----------|
| [         |
|   "aaa",  |
|   "bbb",  |
|   "ccc",  |
|   "ddd",  |
|   "eee"   |
| ]         |
+-----------+

You have to use the Snowflake array function to extract required value from an array of string.

For example, extract first value from the string array.

SELECT GET(SPLIT(col1, ','), 0) as str_array
FROM (SELECT 'aaa,bbb,ccc,ddd,eee' AS col1) AS a;

+-----------+
| STR_ARRAY |
|-----------|
| "aaa"     |
+-----------+

Note that, array index starts from 0.

Snowflake STRTOK Function

The STRTOK Function tokenizes a given string and returns the requested part.

If the part does not exist, then function will return NULL. If any parameter is NULL, then NULL is returned.

Following is the STRTOK function syntax

STRTOK(<string> [,<delimiter>] [,<partNr>])

where, the string is the input string. The delimiter is the string delimiter and partnr is part number to be returned.

For example, consider below SQL statement to split string on comma delimiter and return the first part of the string.

SELECT STRTOK(col1, ',', 1) as firstValue 
FROM (SELECT 'aaa,bbb,ccc,ddd,eee' AS col1);

+------------+
| FIRSTVALUE |
|------------|
| aaa        |
+------------+

Snowflake SPLIT_TO_TABLE Function

The SPLIT_TO_TABLE table function splits a string based on a specified delimiter and flattens the results into rows.

Following is the SPLIT_TO_TABLE function syntax

SPLIT_TO_TABLE(<string>, <delimiter>)

where, the string is the input string. The delimiter is the string delimiter.

For example, consider below SQL statement to split string on comma delimiter and return rows consist of a fixed set of columns.

select table1.value as col 
from table(split_to_table('aaa,bbb,ccc,ddd,eee', ',')) as table1;

+-----+
| COL |
|-----|
| aaa |
| bbb |
| ccc |
| ddd |
| eee |
+-----+

You can also use FLATTEN function to convert array to rows.