How to use CONCAT function in Snowflake

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  |
*/

Related articles

Snowflake coalesce
Snowflake replace
Snowflake substring
Snowflake ifnull
Select sample of rows in Snowflake

References

Snowflake concat documentation