Categories:

String & binary functions (General)

CONCAT_WS

Concatenates two or more strings, or concatenates two or more binary values, and uses the first argument as a delimiter between the following strings.

Note

Unlike some implementations of the CONCAT_WS function, the Snowflake CONCAT_WS function doesn’t skip NULL values.

See also:

CONCAT

Syntax

CONCAT_WS( <separator> , <expression> [ , <expression> ... ] )
Copy

Arguments

separator

The separator must meet the same requirements as expression.

expression

The input expressions must be all strings, or all binary values.

Returns

The function returns a VARCHAR or BINARY value that contains the 2nd through Nth arguments, separated by the first argument.

If any argument is NULL, the function returns NULL.

The data type of the returned value is the same as the data type of the input values.

Usage notes

  • Metadata functions such as GET_DDL accept only constants as input. Concatenated input generates an error.

  • CONCAT_WS puts separators between arguments, not after the last argument. If CONCAT_WS is called with only one argument after the separator, then no separator is appended.

Collation details

Examples

Call the CONCAT_WS function to concatenate three strings with a comma separator:

SELECT CONCAT_WS(',', 'one', 'two', 'three');
Copy
+---------------------------------------+
| CONCAT_WS(',', 'ONE', 'TWO', 'THREE') |
|---------------------------------------|
| one,two,three                         |
+---------------------------------------+

The following example shows that if any argument is NULL, the function returns NULL:

SELECT CONCAT_WS(',', 'one', NULL, 'two');
Copy
+------------------------------------+
| CONCAT_WS(',', 'ONE', NULL, 'TWO') |
|------------------------------------|
| NULL                               |
+------------------------------------+

The following example shows that when there is only one string to concatenate, the CONCAT_WS function doesn’t append a separator:

SELECT CONCAT_WS(',', 'one');
Copy
+-----------------------+
| CONCAT_WS(',', 'ONE') |
|-----------------------|
| one                   |
+-----------------------+
Language: English