Snowflake Concat Function and Operator

The Snowflake cloud architecture supports data ingestion from multiple sources, hence it is a common requirement to combine data from multiple columns to come up with required results. You may also get a requirement to concatenate multiple strings before loading them to target table. For example, you may get requirement to combine state and city columns before loading data to the customer address table. In this article, we will check Snowflake CONCAT function, its Syntax and examples. We will also check how to combine two or more columns using Snowflake CONCAT operator (||).

To be in sync with other relational databases such as Teradata, Snowflake, Redshift, Snowflake supports multiple methods to concatenate two or more columns or string values.

The Snowflake supports following two methods to concatenate columns or strings.

Snowflake Concat Functions
Snowflake Concat Operator (||)

Now, let us check these two methods briefly.

Snowflake Concat Function

The CONCAT function in Snowflake concatenates two character strings and returns the resulting string. Note that, If any of the values is null, the result is also null.

Following is the concat function syntax

CONCAT( <expr1> [ , <exprN> ... ] )

Where, expr can be a column name or string value.

Snowflake Concat Function Example

Following example demonstrates the concatenation of two columns.

SELECT Concat(ca_state, ',', ca_country) AS state_country
FROM   customer_address
LIMIT  5; 
+------------------+
| STATE_COUNTRY    |
+-------------------+
| STATE_COUNTRY     |
|-------------------|
| OH, United States |
| LA, United States |
| SD, United States |
| AK, United States |
| PA, United States |
+-------------------+

Similarly, following example demonstrates the concatenation of two strings.

SELECT CONCAT('KA', ', ', 'India') as state_country;
+---------------+
| STATE_COUNTRY |
|---------------|
| KA, India     |
+---------------+

Snowflake Nested CONCAT Functions

You don’t have to use nested concat functions as Snowflake concat supports multiple columns as an argument. But, if your requirement is to use nested concat functions then same is supported.

For example,

select CONCAT('Nested', CONCAT(' CONCAT', ' example!')) as nested_concat;
+------------------------+
| NESTED_CONCAT          |
|------------------------|
| Nested CONCAT example! |
+------------------------+

Snowflake CONCAT_WS Function

The concat_ws function concatenates two or more strings, or concatenates two or more binary values and adds separator between those strings.

The CONCAT_WS operator requires at least two arguments, and uses the first argument to separate all following arguments

Following is the concat_ws function syntax

CONCAT_WS( <separator> , <expression1> [ , <expressionN> ... ] )

Snowflake Concat_ws Function Example

Following example demonstrates the concatenation of two columns and adds separator “-“.

SELECT CONCAT_WS('-', 'KA','India') as state_country;
+---------------+
| STATE_COUNTRY |
|---------------|
| KA-India      |
+---------------+

Snowflake Concat Operator (||)

The concatenation operator concatenates two strings on either side of the || symbol and returns the concatenated string. The || operator provides alternative syntax for CONCAT and requires at least two arguments.

For example,

select 'Nested' || ' CONCAT' || ' example!' as Concat_operator;
+------------------------+
| CONCAT_OPERATOR        |
|------------------------|
| Nested CONCAT example! |
+------------------------+

Handling NULL Values in CONCAT function and the Concatenation operator

For both the CONCAT function and the concatenation operator, if one or both strings are null, the result of the concatenation is null.

For example,

-- Concat function with NULL
select concat('Bangalore, ', NULL) as null_example;
+--------------+
| NULL_EXAMPLE |
|--------------|
| NULL         |
+--------------+

-- Concat operator with NULL
select 'Bangalore, '|| NULL as null_example;
+--------------+
| NULL_EXAMPLE |
|--------------|
| NULL         |
+--------------+

Therefore, it is always better to use Snowflake NULL handling functions to deal with null values in your data.

For example,

-- Concat function with NULL handling
select concat('Bangalore, ', NVL(NULL,'')) as null_example;
+--------------+
| NULL_EXAMPLE |
|--------------|
| Bangalore,   |
+--------------+

-- Concat operator with NULL handling
select 'Bangalore, '|| NVL(NULL, '') as null_example;
+--------------+
| NULL_EXAMPLE |
|--------------|
| Bangalore,   |
+--------------+