Using the Split Function in Snowflake

How to easily work with Arrays as Data Storage

Photo by James Lee on Unsplash

If you work with modern Data Warehouses like Snowflake, Google BigQuery, Amazon Redshift & Co., you will come into contact with arrays quite often during data integration and analysis. The SPLIT function in Snowflake SQL is a powerful tool that allows you to split a string into multiple substrings based on a specific separator. In this article, we will explain the syntax of the SPLIT function and give some examples of its usage.

SYNTAX

The syntax of the SPLIT function in Snowflake SQL is as follows:

SPLIT(text, delimiter )

Here, ‘text’ is the string to be separated and ‘delimiter’ is the character or string you want to use as a delimiter — like a , (comma) or a ; (semicolon) for example. The SPLIT function returns an array of substrings separated by the specified delimiter.

Example using the Split-Function

Let's take a look at an example to see how the SPLIT function works in practice:

SELECT SPLIT('apple,banana,orange', ',');

In this example, I splitted the string ‘apple,banana,orange’ into an array of substrings using the comma as the delimiter. The outcome would look like…