ClickHouse concat Function: Syntax, NULL Handling, and Examples

The concat function in ClickHouse joins any number of arguments into a single string. Non-string arguments are converted to their default string serialization, so you can pass numbers, dates, or arrays without an explicit cast. It returns Nullable(String): if any argument is NULL, the result is NULL. Use it whenever you need to build composite keys, format output, or stitch columns together for export.

Syntax

concat(s1, s2[, s3, ...])

The single-argument form concat(x) simply returns the string representation of x. See the official ClickHouse string functions reference for the canonical definition.

Parameters

Return type: Nullable(String) if any argument is nullable; otherwise String. Available since v1.1.0.

Examples

Basic concatenation

SELECT concat('Hello', ', ', 'World') AS greeting;
-- Output: Hello, World

Mixing strings and numbers

SELECT concat('user_', toString(user_id), '@', domain) AS email
FROM users
LIMIT 3;

Numbers are converted via toString() implicitly when used with concat, but writing the cast explicitly makes intent clear.

NULL handling

SELECT concat('a', NULL, 'b') AS result;
-- Output: NULL

Any NULL argument propagates to a NULL result. To treat NULLs as empty strings, wrap each nullable argument with `coalesce` or ifNull:

SELECT concat('a', coalesce(middle_name, ''), 'b') FROM names;

Concatenation with a separator

For comma-separated lists or path joins, use concatWithSeparator (alias concat_ws) instead of repeating the delimiter:

SELECT concatWithSeparator(', ', first_name, last_name, city) AS row
FROM customers;

Aggregating values into a delimited string

To collect grouped values, use `groupArray` plus arrayStringConcat:

SELECT user_id, arrayStringConcat(groupArray(product_id), ',') AS purchases
FROM orders
GROUP BY user_id;

Common Issues and Gotchas

• NULL propagation differs from PostgreSQL. Postgres treats NULL || 'x' as 'x' only when both sides are non-null; ClickHouse's concat returns NULL whenever any argument is NULL. A concat_null_yields_null setting to align with Postgres behavior has been proposed.
• The || operator is a shorthand for concat. 'a' || b compiles to concat('a', b) and inherits the same NULL behavior.
• GROUP BY concat(...) can be slow. When the result of concat is used as a grouping key, ClickHouse cannot use the source column's primary index. Use concatAssumeInjective to tell the optimizer the function is injective, which enables the optimize_injective_functions_in_group_by rewrite. Only safe when no two distinct input tuples produce the same output.
• FixedString padding leaks through. Concatenating a FixedString(N) column includes trailing zero bytes unless you trim first with trim(BOTH '\0' FROM ...).

Performance Notes

concat is vectorized and runs at near-memcpy throughput on String columns. The slow path is implicit conversion: concat(int_col, str_col) allocates a temporary String for each row. If the same concatenation appears in many queries, materialize it as a column (a `MATERIALIZED` expression or a projection) so the work happens once at insert time.

Frequently Asked Questions

Q: How does concat differ from concatWithSeparator?
A: concat joins arguments with no separator, while concatWithSeparator(sep, s1, s2, ...) inserts sep between each pair. The latter has an _ws alias (concat_ws) borrowed from MySQL.

Q: Does ClickHouse concat handle NULL values?
A: concat returns NULL if any argument is NULL. To substitute a default, wrap each nullable input with coalesce(col, '') or ifNull(col, '') before passing it to concat.

Q: Can concat take non-string types?
A: Yes. Numbers, dates, arrays, tuples, and most other types are converted using their default serialization. The conversion is implicit but copies data, so an explicit toString() is no slower and reads more clearly.

Q: What is concatAssumeInjective and when should I use it?
A: concatAssumeInjective is identical to concat at runtime but flags the function as injective for the query optimizer. This enables index-aware GROUP BY rewrites. Use it only when the inputs guarantee no collisions; otherwise grouping results are wrong.

Q: Is there a limit on the number of arguments to concat?
A: No documented hard cap, but each argument adds parser work and (for non-strings) a conversion call. For very wide concatenations, building an array and calling arrayStringConcat is usually faster.

Q: Why does my query return Nullable(String) when I only pass plain Strings?
A: If any column in the call has Nullable(...) in its type, the result type is promoted to Nullable(String) even when all observed values are non-NULL. Cast with assumeNotNull(col) if you can guarantee the column is never NULL.

Pulse Monitors ClickHouse String Workloads

String functions such as concat are CPU-bound and can dominate query time on wide tables. Pulse provides AI-powered monitoring for ClickHouse, surfacing slow queries, hot CPU patterns, and memory pressure with automated root-cause analysis before dashboards start timing out.

Related Reading

• ClickHouse coalesce Function: replace NULLs with defaults before concatenating
• ClickHouse substring Function: companion string-extraction function
• ClickHouse lower Function: case-normalize before concatenating keys
• ClickHouse replaceAll Function: substitute characters inside concatenated strings
• ClickHouse groupArray Function: aggregate values into an array for delimited output
• ClickHouse MergeTree: how primary-key columns interact with expression grouping
• ClickHouse Projections Guide: precompute concatenated columns at insert time