Snowflake concat function

Snowflake concat function, explained

Concat is a transformation function in Snowflake. In this post I will walk through what it’s useful for and how to use it in your database.

Stephen Allwright
Stephen Allwright

Concat is a transformation function in Snowflake, in its simplest form all you need to run is select concat(column1,column2) from table. In this post I will walk through what it’s useful for and how to use it in your database.

Snowflake concat, what is it?

Concat is a function which takes a collection of columns or values and returns a concatenated string. This sounds similar to the listagg function, but differs as the concat function concatenates along row wise, whilst listagg concatenates up column wise to a list.

How do you concatenate in Snowflake?

Concatenating using the concat function in Snowflake is incredibly easy. The syntax for running it is simply:

concat( col_1 , col_2 , ... , col_n )

There are a few things that you should be aware of when using this function:

  • If one of the values is null then the resulting string is null
  • Concat only accepts string or binary values, and the values used in the function need to either all be string or all be binary values
  • The returned data type is either a string or a binary, and mirrors the input data type

Snowflake concat with a delimiter

The Snowflake concat function does not have a delimiter parameter, so if you want to concatenate your columns with a defined delimiter then this needs to be done manually.

This can be done with the following syntax:

select concat(col_1, ",", col_2, ",", col_3) from table_name

select concat(col_1, " - ", col_2, " - ", col_3) from table_name

When should you use concat in Snowflake?

Concat is a very common function to use in real world use cases, so knowing how to implement it is incredibly valuable. I will illustrate a simple use case where we want to concatenate all category information for products using a custom delimiter:

/*
products table

| Product | Category_1  | Category_2 |
|---------|-------------|------------|
| Milk    | Dairy       | Milk       |
| Butter  | Dairy       | Butter     |
| Banana  | Fruit & Veg | Fruit      |
| Carrot  | Fruit & Veg | Vegetable  |
*/

select 
product, 
concat(category_1, ",", category_2) as concat_category
from prodcuts

/*
| product |    concat_category     |
|---------|------------------------|
| Milk    | Dairy,Milk             |
| Butter  | Dairy,Butter           |
| Banana  | Fruit & Veg,Fruit      |
| Carrot  | Fruit & Veg,Vegetable  |
*/

Snowflake coalesce
Snowflake replace
Snowflake substring

References

Snowflake concat documentation

Snowflake

Stephen Allwright Twitter

I'm a Data Scientist currently working for Oda, an online grocery retailer, in Oslo, Norway. These posts are my way of sharing some of the tips and tricks I've picked up along the way.

Comments