CONCAT

Concatenate two or more arrays, strings, or binary values into a single value.

Category: collectionReturns: ARRAY<T> | STRING | BINARYDialect: Standard

Syntax

CONCAT(expr1, expr2, ...)

Description

## Overview Concatenates two or more arguments of the same collection or string type into a single value. CONCAT is the polymorphic concatenation primitive and is the preferred function when the argument type can be either STRING or ARRAY depending on context. Typical uses include merging two or more arrays into one, composing strings from column values and literals, and building binary payloads by catenation. For array inputs the behaviour mirrors ARRAY_CAT generalised to n operands. ## Behavior - For arrays, returns an ARRAY<T> containing all elements from the operands in order. - For strings, returns a STRING formed by appending each operand. - For binary inputs, returns a BINARY value formed by catenation. - Element or character ordering is deterministic: operand 1 first, operand 2 next, and so on. - Duplicates are not removed for array inputs. - Element types across array operands must be compatible; result type is the common super-type. ## Null and empty handling - Array case: if any operand is NULL, the result is NULL. - String case: NULL operands are treated as empty strings in most string dialects; however, in strict mode a NULL operand may propagate NULL. Consult the session setting when in doubt. - Empty array operand is a no-op; the result equals concatenation of the remaining operands. - NULL elements inside arrays are preserved in place. ## Compatibility - Matches the SQL standard CONCAT convention for strings. The array form generalises ARRAY_CAT to more than two operands. - The || operator is a commonly-accepted infix synonym for concatenation where supported.

Parameters

Name	Type	Description
`expr`		Specifies two or more arrays, strings, or binary values to concatenate. All arguments must share a compatible type (all arrays of the same element type, or all strings, or all binary).

Examples

-- Concatenate arrays
SELECT CONCAT(ARRAY[1, 2], ARRAY[3, 4]);  -- [1, 2, 3, 4]

-- Concatenate three arrays
SELECT CONCAT(ARRAY['a'], ARRAY['b'], ARRAY['c']);  -- ['a', 'b', 'c']

-- Concatenate strings
SELECT CONCAT('Hello', ' ', 'World');  -- 'Hello World'

-- NULL elements inside arrays are preserved
SELECT CONCAT(ARRAY[1, CAST(NULL AS INT)], ARRAY[3]);  -- [1, NULL, 3]

-- Merge a per-session event array with a tail sentinel array
SELECT session_id, CONCAT(events, ARRAY['session_closed']) AS trail
FROM analytics.events.user_sessions;

-- NULL argument propagates in the array case
SELECT CONCAT(CAST(NULL AS ARRAY<INT>), ARRAY[1]);  -- NULL

Pitfalls

For array inputs, any NULL operand makes the result NULL. Use COALESCE(col, ARRAY()) to coerce NULL arrays to empty arrays when that behaviour is desired.
CONCAT does not deduplicate. Use ARRAY_UNION if duplicates should collapse.
The behaviour of NULL operands differs between string and array contexts. Document the expectation in code comments when mixing types.
Element types must be compatible across array operands. Mixing ARRAY<INT> with ARRAY<STRING> raises a type error rather than auto-casting.