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.
Related Articles,
- Snowflake Extract Function Usage and Examples
- Regular Expression Functions in Snowflake
- Snowflake Pad Zeros – LPAD and RPAD with Examples
- How to combine two arrays in Snowflake?
- Snowflake NULL Handling Functions, Usage and Examples
Hope this helps 🙂