HOW TO: Use Snowflake CONCAT to Combine Strings (2024)

Snowflake offers a variety of string functions to work with string data. Among the top Snowflake string functions, Snowflake CONCAT is one of them, which helps to join two or more strings together to form a new string.

In this article, we will cover everything you need to know about the Snowflake CONCAT function. We’ll cover syntax, data type handling, real-world examples, performance implications, limitations—so and more!!

What is Snowflake CONCAT Function?

Snowflake CONCAT function concatenates two or more string values together into a single string output. The syntax allows passing multiple string arguments, merging them end-to-end in the specified order.

For use cases like adding string/text together from different columns, formatting outlier values, or building customized strings for display/report purposes, Snowflake concat enables this directly in SQL without complex/manual string or regex operations.

How does CONCAT function work?

Snowflake CONCAT function accepts an unspecified number of string arguments as inputs. The syntax is very straightforward:

CONCAT(<expr1>, <expr2>,...)

Where each <expr> can be any string literal or expression that evaluates to a string.

Some examples:

Example 1:

SELECT CONCAT('Hi', 'there');

The output:

Example 2:

CONCAT(FIRST_NAME, ' ', LAST_NAME)

Would concatenate the content of two text columns with a space between.

As you can see, we simply specify comma-separated strings as arguments, and the result combines them in the same order.

We will cover the examples in detail in the later section.

Data type supported by CONCAT function

A key aspect of concat is its support for string data types. It can accept and concatenate all Snowflake string variants:

• STRING
• VARCHAR
• CHAR
• TEXT

The return type is always STRING, regardless of input types. Literal strings get coerced to STRING as well.

One catch is numeric or non-string types are not directly compatible. We could explicitly cast them using TO_VARCHAR to enable concatenation.

How does CONCAT handle null values?

One of the most important considerations with Snowflake concat is the handling of NULL values. Concatenating a null string with another non-null string results in a NULL output.

Let’s test it out:

CONCAT(NULL, 'Text')

You can see that, this can lead to unintended nulls in output if not properly handled. Strategies like COALESCE can be used to replace nulls with an alternative value first.

Practical Examples

To better understand real-world usage, let’s walk through some applied examples of leveraging Snowflake’s concat function.

Example 1—Basic String Concatenation

The simplest usage is combining literal string values, like creating a full name:

SELECT CONCAT('Chaos', ' ', 'Genius')

As you can see, this shows concatenating three strings with spaces between to build a single string.

Example 2—Concatenating Table Columns

Typically, concat is used to combine column values from a table. One common example is generating full names:

CREATE TABLE customers (
  first_name VARCHAR,
  last_name VARCHAR
);

INSERT INTO customers
  (first_name, last_name)
VALUES
  ('Chaos', 'Genius'),
  ('Preeti', 'Shrimal'),
  ('Genius', 'Chaos');
  
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM customers;

So you can see that by concatenating two string columns with a space between, we can conveniently create a full name string without complex/manual string operations.

Example 3—Concatenating Strings and Non-String Data Types

While Snowflake concat expects string inputs, we can craft explicit casts to allow numeric or other data types:

CREATE TABLE products (
  id NUMBER,
  product_name VARCHAR
);

INSERT INTO products 
  (id, product_name)
VALUES
  (1, 'Apples'),
  (2, 'Oranges'),
  (3, 'Bananas');
  
SELECT CONCAT('ID: ', TO_VARCHAR(id)) AS id_str
FROM products;

Here we cast the numeric ID column using TO_VARCHAR to become string-compatible before concatenating using Snowflake concat function.

Example 4—Nested String Concatenations

With Snowflake concat supporting an unspecified number of arguments, we can also nest concats to build complex strings:

CREATE TABLE mytable (
  col1 VARCHAR, 
  col2 VARCHAR  
);

INSERT INTO mytable 
  (col1, col2)
VALUES
  ('foo', 'bar'),
  ('apple', 'banana');

SELECT 
  CONCAT('Start_', 
         CONCAT(col1, '_'),  
         col2) AS nested_concat
FROM mytable;

As you can see, by nesting Snwoflake concat functions, we concat COL1 and COL2 values while also prefixing and suffixing additional strings.

When to Use CONCAT Function?

Here are a few key use cases that can help clarify appropriate applications for Snowflake concat function:

• Combining text columns with string literals like IDs, names, or messages
• Appending or prefixing strings for data transformations
• Cleansing text strings into proper formats
• Custom building string outputs for display purposes

What we want to avoid is overusing Snowflake concat where simpler string operators like || would suffice. Evaluating each expression has a small cost, so for basic use cases concat may be overkill.

What is the difference between CONCAT_WS and CONCAT Snowflake?

An important alternative to understand is CONCAT_WS which stands for Concatenate With Separator. The _ws version allows explicitly defining a separator string between the concatenated values.

For a simple example:

SELECT CONCAT_WS('-', 'one', 'two', 'three')

With Snowflake concat, the values just directly combine without any separators.

So Snowflake Concat_ws addresses use cases where a separator is needed when combining string values end-to-end.

Here is a comparison of the key differences between Snowflake CONCAT_WS and Snowflake CONCAT function:

Limitations of Snowflake CONCAT Function

Snowflake concat is fully optimized and widely used but there are some limitations worth keeping in mind:

• Performance can degrade with high numbers of very large strings
• Lack of options for string formatting or more advanced formatting
• Separator specification requires using Concat_ws version
• Not compatible with non-string data types without explicit casting

Always be very careful not to abuse Snowflake concat where simpler approaches would work fine. Evaluate if large strings can be pre-processed before hitting Snowflake as well.

Conclusion

And that’s a wrap! Concatenating strings is a super common task when wrangling text data. As more and more unstructured data piles up in Snowflake, the ability to smoothly combine text fields, columns—and literals can be a lifesaver! Luckily, Snowflake concat function makes string concatenation easy. No fussing with complex syntax or manual regex operations. Just list out the values you want smashed together, separated by commas. Snowflake handles the heavy lifting behind the scenes.

In this article, we covered:

• What is Snowflake CONCAT Function?
• How does Snowflake CONCAT function Work?
• Data type supported by Snowflake CONCAT
• How does Snowflake CONCAT handle null values?
• Practical Examples and Use Cases of Snowflake CONCAT Function
• When to Use Snowflake CONCAT Function?
• What is the difference between Concat_ws and Snowflake Concat?
• Limitations of Snowflake CONCAT Function

Snowflake concat makes string wrangling so much smoother. Just be careful with nulls, watch for performance hiccups—and always make use of concat_ws when you need those separators.

FAQs

What is the Snowflake CONCAT function?

Snowflake CONCAT function joins two or more string values together into a single string output. It merges multiple text arguments sequentially in the order specified.

How does Snowflake CONCAT function work?

Snowflake CONCAT function works by accepting multiple string literals or expressions separated by commas. It concatenates them together in the same order to output a single combined string.

What are the data types supported by Snowflake CONCAT?

Snowflake CONCAT supports concatenating all Snowflake string data types — STRING, VARCHAR, CHAR, and TEXT. The return type is always STRING.

Does Snowflake CONCAT work with non-string data types?

No, Snowflake CONCAT does not directly support non-string types. Numeric and other types must be explicitly cast using TO_VARCHAR() to make them compatible.

How does CONCAT handle NULL values in Snowflake?

Concatenating a NULL value with non-NULL strings results in a NULL output. COALESCE can be used to replace NULLs before using Snowflake CONCAT.

What is the difference between CONCAT and CONCAT_WS in Snowflake?

Snowflake CONCAT_WS allows specifying a custom separator, while Snowflake CONCAT just sequentially concatenates without separators.

Does Snowflake CONCAT function concat after the last argument?

No, CONCAT does not put separators after the last concatenated argument.

How do you concatenate two columns in Snowflake SQL?

You can make use of Snowflake CONCAT(column1, ' ', column2) to join two columns with a space between each value into a single string.

How do I concatenate a string and a number in Snowflake?

Cast the number to a string using TO_VARCHAR() before concatenating with Snowflake CONCAT function..

Is CONCAT faster than || operator in Snowflake?

No, Snowflake CONCAT has a higher invocation cost compared to || for basic concatenation. Use || operator unless you specifically need CONCAT's functionality.

Can I nest CONCAT functions in Snowflake?

Yes, Snowflake allows arbitrarily nesting Snowflake CONCAT functions to achieve complex string-building logic.

Does CONCAT have formatting options in Snowflake?

No, CONCAT does not offer advanced formatting of the output string beyond basic concatenation. Use other functions for formatting.

What are the limitations of the Snowflake CONCAT function?

Performance issues with massive strings, lack of advanced formatting—and no separator support without using CONCAT_WS.