What is CONCAT() function in Snowflake ?

Are you looking to find what is the CONCAT() function within the Snowflake cloud data warehouse or maybe you are looking for a solution about how to use the concat functions in the Snowflake? You may also want to check what could alternatives where you wanted to use the CONCAT function.  I will also show you what is the difference between the CONCAT function and the CONCAT operator. I will explain the CONCAT function by taking a practical example. So don’t waste time let’s start step by step guide to understanding what is the concat expression.

CONCAT() Function is used to concatenates one or more strings/binary values. If any of the value is null then the result will also be null. The data type of returned value is same as input value data type.

CONCAT() function consists of one or more strings i.e.. one or more arguments depending on the user requirement.

Syntax of CONCAT Function in Snowflake

-- Syntax : 

CONCAT( expression-one,[ expression-two,….,expression-n ])

CONCAT Function argument details

CONCAT function example in Snowflake :

-- Example 1 : 

SELECT CONCAT('Anna','Rose')

-- Query Result :

+-----------------------+
| CONCAT('ANNA','ROSE') |
|-----------------------|
| AnnaRose              |
+-----------------------+

-- Example 2 : 

SELECT CONCAT('Anna')

-- Query Result :

+----------------+
| CONCAT('ANNA') |
|----------------|
| Anna           |
+----------------+

-- Example 3 : 

SELECT CONCAT('Anna', 'Rose', null)

-- Query Result :

+------------------------------+
| CONCAT('ANNA', 'ROSE', NULL) |
|------------------------------|
| NULL                         |
+------------------------------+

Alternative of CONACT Function in Snowflake:

Instead of CONCAT function we can also use || operator which provides same action. Here we need to use at least two arguments.

-- Syntax : using || operator 

Select expression-one || expression-two || expression-n

-- Example :

select 'John'||'Rose'

-- Query Result :

+----------------+
| 'JOHN'||'ROSE' |
|----------------|
| JohnRose       |
+----------------+

What is the difference between CONCAT function and operator?

How to concatenate string with separator in the Snowflake?

Using the CONCAT function in special manner we can concatenate the string with the separator in the Snowflake. Instead simply giving the multiple strings within the concat function, for example you can use the separator along with the string in the concat function.

Example of concat string with separator in Snowflake

-- Example :

select CONCAT('hello' , ','  , 'hi',  ',' ,   'how are you');

-- Output
+-----------------------------------------------+
| CONCAT('HELLO',',','HI', ',', ' HOW ARE YOU') |
|-----------------------------------------------|
| hello,hi, how are you                         |
+-----------------------------------------------+
1 Row(s) produced. Time Elapsed: 2.274s

Example of concat string with | as separator in Snowflake

-- Example :

select CONCAT('hello','|','hi', '|', ' how are you');

-- Output
+-----------------------------------------------+
| CONCAT('HELLO','|','HI', '|', ' HOW ARE YOU') |
|-----------------------------------------------|
| hello|hi| how are you                         |
+-----------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.613s

Snowflake concat with Null

When you concat any valid string with null string then it results in the NULL only. It means that concatenation with null always give the result as null.

Example of Concat with Null in Snowflake

Let’s concat a valid string with one null value as follows:

select CONCAT('hello',' Hi', ' How are you', null) as Result;

-- Output
+--------+
| RESULT |
|--------|
| NULL   |
+--------+
1 Row(s) produced. Time Elapsed: 0.298s

Snowflake official Documentation for CONCAT function Link

Final Thoughts

In this article we have learned about CONCAT function and also alternative ways to perform the same action with examples. I have also covered the difference between CONCAT function and operator. I hope the information that was provided is helped in gaining the knowledge.

• For Azure Study material Join Telegram group : Telegram group link:
• Azure Jobs and other updates Follow me on LinkedIn: Azure Updates on LinkedIn
• Azure Tutorial Videos: Videos Link

• Date_Trunc() in Snowflake
• Date_Add() in Snowflake
• Parse_Json() in Snowflake
• Insert() in Snowflake
• Right() in Snowflake
• StartsWith() in Snowflake
• Replace () in Snowflake
• Position() function
• ListAgg() function
• Like() Function
• LikeAll() Function
• Like Any() Function
• Current_Date() Function
• Row_Number() Function
• Cast() Function
• Show() Function
• ISNULL() Function
• Lag() Function
• ILIKE() Function
• DateDiff() Function
• Substring() Function
• ILIKE ANY() Function
• Round() Function
• To_Date() Function
• Concat() Function
• Length() Function
• TRIM(), LTRIM(), RTRIM()
• SPLIT() Function
• Reverse() Function
• Repeat() Function
• Unicode() Function
• IFNULL() Function
• Pivot() Function
• IFF() Function

Revanth Kumar