Categories:

String & Binary Functions (General)

CONCAT_WS

Concatenates two or more strings, or concatenates two or more binary values. If any of the values is null, the result is also null.

The CONCAT_WS operator requires at least two arguments, and uses the first argument to separate all following arguments.

See also:

CONCAT

Syntax

CONCAT_WS( <separator> , <expression1> [ , <expressionN> ... ] )

Arguments

separator

The separator must meet the same requirements as expressionN.

expressionN

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

Returns

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

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

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

Concatenate three strings with separator:

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

When there is only one string to concatenate, CONCAT_WS does not append a separator:

SELECT CONCAT_WS(',', 'one');
+-----------------------+
| CONCAT_WS(',', 'ONE') |
|-----------------------|
| one                   |
+-----------------------+