CONCAT_WS
Description
The CONCAT_WS function (Concatenate With Separator) concatenates multiple strings or arrays using a specified separator. Unlike the CONCAT function, CONCAT_WS automatically skips NULL values (but not empty strings) and inserts a separator between non-NULL values. This function supports both string arguments and array arguments modes, and is very useful in scenarios such as generating CSV formats, path joining, and tag lists.
Syntax
-- String mode
CONCAT_WS(<sep>, <str> [, <str> ...])
-- Array mode
CONCAT_WS(<sep>, <array> [, <array> ...])
Parameters
| Parameter | Description |
|---|---|
<sep> | Separator string used to join parts. Type: VARCHAR |
<str> | String arguments to be concatenated. Type: VARCHAR |
<array> | Array arguments to be concatenated, array elements must be string type. Type: ARRAY<VARCHAR> |
Return Value
Returns VARCHAR type, representing the concatenated string with separators.
Concatenation rules:
- Uses the first argument as the separator to join subsequent arguments
- Automatically skips NULL values, but preserves empty strings
- Supports string arguments or array arguments, but cannot be mixed
- Supports UTF-8 multi-byte characters as separators and content
Special cases:
- If the separator is NULL, returns NULL
- If all arguments to be concatenated are NULL, returns an empty string
- In array mode, skips NULL elements in arrays, but preserves empty string elements
- When NULL arrays are included in multiple array arguments, returns an empty string
- Mixing string arguments and array arguments is not allowed
Examples
- Basic string concatenation
SELECT CONCAT_WS(',', 'apple', 'banana', 'orange'), CONCAT_WS('-', 'hello', 'world');
+-------------------------------------------+----------------------------------+
| CONCAT_WS(',', 'apple', 'banana', 'orange') | CONCAT_WS('-', 'hello', 'world') |
+-------------------------------------------+----------------------------------+
| apple,banana,orange | hello-world |
+-------------------------------------------+----------------------------------+
- NULL separator handling
SELECT CONCAT_WS(NULL, 'd', 'is'), CONCAT_WS('or', 'd', NULL, 'is');
+----------------------------+----------------------------------+
| CONCAT_WS(NULL, 'd', 'is') | CONCAT_WS('or', 'd', NULL, 'is') |
+----------------------------+----------------------------------+
| NULL | doris |
+----------------------------+----------------------------------+
- Empty string handling (preserves empty strings)
SELECT CONCAT_WS('|', 'hello', '', 'world', NULL), CONCAT_WS(',', '', 'test', '');
+--------------------------------------------+-------------------------------+
| CONCAT_WS('|', 'hello', '', 'world', NULL) | CONCAT_WS(',', '', 'test', '') |
+--------------------------------------------+-------------------------------+
| hello||world | ,test, |
+--------------------------------------------+-------------------------------+
- All NULL values
SELECT CONCAT_WS('x', NULL, NULL), CONCAT_WS('-', NULL, NULL, NULL);
+----------------------------+---------------------------------+
| CONCAT_WS('x', NULL, NULL) | CONCAT_WS('-', NULL, NULL, NULL) |
+----------------------------+---------------------------------+
| | |
+----------------------------+---------------------------------+
- Array mode basic usage
SELECT CONCAT_WS('or', ['d', 'is']), CONCAT_WS('-', ['apple', 'banana', 'cherry']);
+------------------------------+--------------------------------------------+
| CONCAT_WS('or', ['d', 'is']) | CONCAT_WS('-', ['apple', 'banana', 'cherry']) |
+------------------------------+--------------------------------------------+
| doris | apple-banana-cherry |
+------------------------------+--------------------------------------------+
- NULL values in arrays
SELECT CONCAT_WS('or', ['d', NULL, 'is']), CONCAT_WS(',', [NULL, 'a', 'b', NULL, 'c']);
+------------------------------------+------------------------------------------+
| CONCAT_WS('or', ['d', NULL, 'is']) | CONCAT_WS(',', [NULL, 'a', 'b', NULL, 'c']) |
+------------------------------------+------------------------------------------+
| doris | a,b,c |
+------------------------------------+------------------------------------------+
- Multiple array concatenation
SELECT CONCAT_WS('-', ['a', 'b'], ['c', NULL], ['d']), CONCAT_WS('|', ['x'], ['y', 'z']);
+------------------------------------------------+----------------------------------+
| CONCAT_WS('-', ['a', 'b'], ['c', NULL], ['d']) | CONCAT_WS('|', ['x'], ['y', 'z']) |
+------------------------------------------------+----------------------------------+
| a-b-c-d | x|y|z |
+------------------------------------------------+----------------------------------+
- NULL array in multiple arrays
SELECT CONCAT_WS('-', ['a', 'b'], NULL, ['c', NULL], ['d']);
+-----------------------------------------------------+
| CONCAT_WS('-', ['a', 'b'], NULL, ['c', NULL], ['d']) |
+-----------------------------------------------------+
| |
+-----------------------------------------------------+
- UTF-8 multi-byte character handling
SELECT CONCAT_WS('x', 'ṭṛì', 'ḍḍumai'), CONCAT_WS('→', ['ṭṛì', 'ḍḍumai', 'hello']);
+-------------------------------+----------------------------------------------+
| CONCAT_WS('x', 'ṭṛì', 'ḍḍumai') | CONCAT_WS('→', ['ṭṛì', 'ḍḍumai', 'hello']) |
+-------------------------------+----------------------------------------------+
| ṭṛìxḍḍumai | ṭṛì→ḍḍumai→hello |
+-------------------------------+----------------------------------------------+
- CSV format generation and path joining
SELECT CONCAT_WS(',', 'Name', 'Age', 'City'), CONCAT_WS('/', 'home', 'user', 'documents', 'file.txt');
+------------------------------------+--------------------------------------------------------+
| CONCAT_WS(',', 'Name', 'Age', 'City') | CONCAT_WS('/', 'home', 'user', 'documents', 'file.txt') |
+------------------------------------+--------------------------------------------------------+
| Name,Age,City | home/user/documents/file.txt |
+------------------------------------+--------------------------------------------------------+
