CONCAT_WS

Concatenate arguments with a separator, skipping any NULL arguments.

Category: stringReturns: VARCHARDialect: Standard

Syntax

CONCAT_WS(sep, str1, str2, ...)

Description

## Overview Concatenates the value arguments with the given separator inserted between each consecutive pair. CONCAT_WS stands for 'concatenate with separator'. Its key feature over a manual string concatenation is that NULL arguments are skipped entirely, not coalesced to an empty string and not causing the separator to appear twice in a row. This makes it the go-to function for composing display fields from optional columns such as a middle name, an apartment number, or a region component of an address. The separator itself is taken literally; it is not a pattern. If the separator is NULL, the entire result is NULL. If every value argument is NULL, the result is an empty string, not NULL. ## Behavior - Returns NULL when the separator is NULL. - Returns an empty string when every value argument is NULL. - Skips NULL values: the separator is inserted only between successive non-NULL values. - Empty-string values are included in the concatenation. The separator will appear on both sides of an empty value. - Non-string values are implicitly cast to their text representation before concatenation. - Operates on Unicode code points, producing valid UTF-8 output. - There is no upper limit on the number of value arguments. ## Compatibility - Matches the common SQL convention for CONCAT_WS. - Related functions: CONCAT concatenates without a separator; STRING_AGG aggregates across rows; ARRAY_TO_STRING joins array elements.

Parameters

Name	Type	Description
`sep`		Specifies the separator inserted between consecutive non-NULL arguments.
`str1`		Specifies the first value to concatenate.
`strN`		Specifies additional values to concatenate. Any number of values may be provided. NULL values are skipped.

Examples

-- Basic comma-separated concatenation
SELECT CONCAT_WS(',', 'a', 'b', 'c');  -- 'a,b,c'

-- NULL values are skipped, separator is not duplicated
SELECT CONCAT_WS(',', 'a', NULL, 'c');  -- 'a,c'

-- Build a full name from optional middle component
SELECT CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name
FROM retail.customers.profiles;

-- All NULLs produce an empty string (not NULL)
SELECT CONCAT_WS(',', NULL, NULL);  -- ''

-- NULL separator yields NULL
SELECT CONCAT_WS(NULL, 'a', 'b');  -- NULL

-- Compose an address line with optional apartment
SELECT CONCAT_WS(', ', street, apartment, city, region) AS address
FROM retail.customers.profiles;

Pitfalls

A NULL separator produces a NULL result. If you want a space-like separator that never yields NULL, COALESCE the separator argument upstream.
CONCAT_WS treats empty strings as real values. CONCAT_WS('-', 'a', '', 'b') returns 'a--b', which is often not what you want. Filter empty strings explicitly if they should be skipped like NULLs.
CONCAT_WS produces an empty string when every value is NULL. A downstream WHERE full_name IS NULL condition will silently not match.
When concatenating numbers, the implicit cast uses the default text representation (for example '3.14' for DOUBLE). If you need a specific format, cast with FORMAT first.