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
nullthen the resulting string is
- 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
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 | */