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 Snowflake 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:

SELECT 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 Types Supported by CONCAT Function

A crucial aspect of CONCAT is its precise handling of data types. It can accept and concatenate all Snowflake string variants:

• STRING
• VARCHAR
• CHAR
• TEXT

Important: The data type of the returned value is the same as the data type of the input value(s).

• If all inputs are VARCHAR, the output is VARCHAR.
• If all inputs are STRING, the output is STRING.
• If inputs are a mix of string types (VARCHAR and STRING), Snowflake determines the result type based on internal precedence rules, typically promoting to STRING if a STRING type is involved.
• If all inputs are BINARY, the output is BINARY.

Numeric or other non-string/non-binary types are not directly compatible. You must explicitly cast them using functions like TO_VARCHAR() or TO_BINARY() to enable concatenation with compatible types.

How Does CONCAT Handle NULL Values?

One of the most important considerations with Snowflake CONCAT is its handling of NULL values. Concatenating a NULL string or binary value with another non-NULL string or binary value results in a NULL output.

Let’s test it out:

CONCAT(NULL, 'Text')

Output:
NULL

You can see that,  this can lead to unintended NULLs in the output if not properly handled. Strategies like COALESCE or IFNULL can be used to replace NULLs with an alternative value (such as an empty string) before concatenation.

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')

Output:

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. A common example is generating full names from first_name and last_name columns:

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;

Output:

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 CONCAT expects compatible string or binary inputs, we can use explicit casts to allow numeric or other data types to be included:

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;

Output:

Here, we cast the numeric id column using TO_VARCHAR to make it string-compatible before concatenating it with other string literals and the product_name column.

Example 4—Nested String Concatenations

With Snowflake CONCAT supporting multiple arguments, you can also nest CONCAT calls 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;

Output:

As you can see, by nesting Snowflake 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 clarify appropriate applications for the Snowflake CONCAT function:

• Combining text columns with string literals like IDs, names, messages or binary headers.
• Appending or prefixing strings for data transformations or standardization.
• Cleansing text strings into proper formats.
• Building customized string outputs for display or reporting purposes.

As an alternative syntax to CONCAT, Snowflake also offers the || operator. Their performances are essentially the same because they both carry out the same underlying concatenation operation. Some people might find the || operator easier to read when concatenating a small number of expressions. Because it clearly lists arguments, CONCAT frequently makes expressions easier to read.

What is the Difference Between CONCAT_WS and CONCAT in 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')

Output:

With CONCAT, the values are just directly appended without any separators. CONCAT_WS addresses use cases where a consistent separator is needed when combining multiple string values.

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

Snowflake String Concatenation: Performance Notes

Snowflake optimizes both the CONCAT function and the || operator, leveraging its underlying architecture for efficient execution.

• CONCAT vs || Operator: Generally speaking, there is no significant performance difference between the CONCAT function and the || operator for simple concatenation. For many situations, they are treated as equivalent by Snowflake's query optimizer. Therefore, your choice often depends on personal preference or coding standards that prioritize readability.
• Argument Count and String Length: Snowflake CONCAT performance can degrade if you concatenate an exceptionally high number of arguments or handle extremely large string values. String manipulation allocates memory and copies data, which consumes more resources as scale increases.
• Data Volume: The overall data volume significantly impacts performance. Concatenating strings across millions or billions of rows will naturally take longer and consume more compute resources than processing a small dataset.
• Warehouse Size: Virtual warehouse size directly affects query performance. Larger warehouses (such as Medium, Large) process data faster, leading to quicker execution of CONCAT operations if the query is complex enough to utilize the additional compute.

Cost of Using Snowflake CONCAT Function

• Compute (credits): Snowflake CONCAT runs on a virtual warehouse and consumes compute credits while the warehouse runs. Credits scale with warehouse size and runtime. Resize warehouses or reduce runtime to cut credit use.
• Warehouse size vs runtime: Bigger warehouses burn more credits per hour but can finish work faster. For heavy, parallelizable string work, a larger warehouse can lower total credits. For lighter work, a smaller warehouse usually costs less. Profile before changing sizes.
• Data volume and string complexity: Concatenating many columns, nested calls, or very large strings increases CPU work, memory allocation, and runtime. That raises compute consumption linearly with work done. Do not assume syntax choice causes major cost differences.
• Storage impact: If you write concatenated results back to tables, longer strings increase stored bytes. Snowflake bills storage monthly on average compressed bytes per day, so persistent large strings raise monthly storage fees. Clean up or compress if you can.
• Cloud Services charges: Snowflake charges Cloud Services credits only when daily Cloud Services usage exceeds 10% of daily warehouse usage. Serverless features do not count toward that 10% adjustment. Expect this to be a small part of most bills unless you run extremely frequent or complex metadata/compilation workloads.

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 a very high number of arguments or extremely large string/binary values, as string manipulation operations can be resource-intensive.
• Snowflake CONCAT lacks options for advanced string formatting (such as padding, alignment, date/number formatting). Other specialized functions should be used for such tasks.
• Explicitly adding a separator requires using CONCAT_WS.
• Snowflake CONCAT is not compatible with non-string or non-binary data types without explicit casting (using TO_VARCHAR() or TO_BINARY()).

Always check to see if CONCAT is the best tool. The || operator is a frequently chosen and functionally equivalent substitute for simplicity in very basic concatenations of only two strings. If performance starts to be an issue, think about pre-processing very large strings.

Save up to 30% on your Snowflake spend in a few minutes!

Request a Demo

Enter your work email

Enter your work email

Request a Demo

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.