How CONCAT_WS() Works in DuckDB

In DuckDB, the CONCAT_WS() function provides an efficient way to join strings with a specified separator. CONCAT_WS() stands for “concatenate with separator”, and many RDBMSs have such a function.

CONCAT_WS() is particularly useful when you need to combine multiple fields or values with a consistent delimiter.

Let’s explore its features and practical applications.

Syntax

The syntax goes like this:

CONCAT_WS(separator, string, ...)

So we provide the separator as the first argument, and then follow this up with all the strings we want to concatenate.

Sample Data

We’ll use the following data for the examples on this page:

-- Create a sample table
CREATE TABLE products (
    id INTEGER,
    category VARCHAR,
    brand VARCHAR,
    model VARCHAR,
    color VARCHAR,
    tags VARCHAR[]
);

-- Insert sample data
INSERT INTO products VALUES
    (1, 'Electronics', 'TechCo', 'Laptop-X1', 'Silver', ARRAY['gaming', 'premium', 'lightweight']),
    (2, 'Accessories', 'GearPro', 'Mouse-M2', NULL, ARRAY['wireless', 'ergonomic']),
    (3, 'Electronics', NULL, 'Tablet-T3', 'Black', ARRAY['budget', 'compact']),
    (4, 'Peripherals', 'ViewTech', 'Monitor-24', 'Black', NULL),
    (5, NULL, 'AudioMax', 'Headset-H1', 'White', ARRAY['bluetooth', 'noise-canceling']);

This data includes NULLs in places that normally shouldn’t contain NULLs, but they will be useful in some of our examples to highlight how CONCAT_WS() handles them.

Basic Usage

Here are a couple of examples that demonstrate the basic usage of the CONCAT_WS() function:

SELECT CONCAT_WS(
    ' - ',
    category,
    brand,
    model
) as product_name
FROM products;

Result:

+-------------------------------------+
|            product_name             |
+-------------------------------------+
| Electronics - TechCo - Laptop-X1    |
| Accessories - GearPro - Mouse-M2    |
| Electronics - Tablet-T3             |
| Peripherals - ViewTech - Monitor-24 |
| AudioMax - Headset-H1               |
+-------------------------------------+

In this case I specified a hyphen as the separator.

Here are some other separators:

SELECT 
    CONCAT_WS(', ', category, brand, model) as comma_separated,
    CONCAT_WS(' | ', category, brand, model) as pipe_separated,
    CONCAT_WS('/', category, brand, model) as path_style
FROM products;

Result:

+-----------------------------------+-------------------------------------+---------------------------------+
|          comma_separated          |           pipe_separated            |           path_style            |
+-----------------------------------+-------------------------------------+---------------------------------+
| Electronics, TechCo, Laptop-X1    | Electronics | TechCo | Laptop-X1    | Electronics/TechCo/Laptop-X1    |
| Accessories, GearPro, Mouse-M2    | Accessories | GearPro | Mouse-M2    | Accessories/GearPro/Mouse-M2    |
| Electronics, Tablet-T3            | Electronics | Tablet-T3             | Electronics/Tablet-T3           |
| Peripherals, ViewTech, Monitor-24 | Peripherals | ViewTech | Monitor-24 | Peripherals/ViewTech/Monitor-24 |
| AudioMax, Headset-H1              | AudioMax | Headset-H1               | AudioMax/Headset-H1             |
+-----------------------------------+-------------------------------------+---------------------------------+

NULL Handling

CONCAT_WS() skips NULL values:

SELECT 
    -- Original values
    category,
    brand,
    model,
    -- CONCAT_WS result
    CONCAT_WS(' - ', category, brand, model) as result
FROM products
WHERE brand IS NULL OR category IS NULL;

Result:

+-------------+----------+------------+-------------------------+
|  category   |  brand   |   model    |         result          |
+-------------+----------+------------+-------------------------+
| Electronics | null     | Tablet-T3  | Electronics - Tablet-T3 |
| null        | AudioMax | Headset-H1 | AudioMax - Headset-H1   |
+-------------+----------+------------+-------------------------+

Empty String Handling

This example compares empty strings with NULL values when they’re passed as strings:

SELECT 
    CONCAT_WS('|', 'A', '', 'B') as empty_string_result,
    CONCAT_WS('|', 'A', NULL, 'B') as null_result,
    CONCAT_WS('|', 'A', '', NULL, 'B') as mixed_result;

Result:

+---------------------+-------------+--------------+
| empty_string_result | null_result | mixed_result |
+---------------------+-------------+--------------+
| A||B                | A|B         | A||B         |
+---------------------+-------------+--------------+

We can see that the empty string causes the separator to be used, whereas the NULL value doesn’t.

Examples of Use Cases

Here are some possible scenarios where the CONCAT_WS() function could come in handy.

Building Hierarchical Paths

We can use CONCAT_WS() to create file-system like paths:

SELECT CONCAT_WS(
    '/',
    'products',
    LOWER(category),
    LOWER(brand),
    LOWER(model)
) as product_path
FROM products
WHERE category IS NOT NULL;

Result:

+------------------------------------------+
|               product_path               |
+------------------------------------------+
| products/electronics/techco/laptop-x1    |
| products/accessories/gearpro/mouse-m2    |
| products/electronics/tablet-t3           |
| products/peripherals/viewtech/monitor-24 |
+------------------------------------------+

Creating CSV-Style Output

SELECT CONCAT_WS(
    ',',
    id::VARCHAR,
    COALESCE(category, ''),
    COALESCE(brand, ''),
    COALESCE(model, ''),
    COALESCE(color, '')
) as csv_row
FROM products;

Result:

+-----------------------------------------+
|                 csv_row                 |
+-----------------------------------------+
| 1,Electronics,TechCo,Laptop-X1,Silver   |
| 2,Accessories,GearPro,Mouse-M2,         |
| 3,Electronics,,Tablet-T3,Black          |
| 4,Peripherals,ViewTech,Monitor-24,Black |
| 5,,AudioMax,Headset-H1,White            |
+-----------------------------------------+

Formatting Display Names

We can use CONCAT_WS() to create readable product names:

SELECT 
    CONCAT_WS(
        ' ',
        brand,
        model,
        CONCAT_WS(
            '/',
            category,
            color
        )
    ) as display_name
FROM products;

Result:

+---------------------------------------+
|             display_name              |
+---------------------------------------+
| TechCo Laptop-X1 Electronics/Silver   |
| GearPro Mouse-M2 Accessories          |
| Tablet-T3 Electronics/Black           |
| ViewTech Monitor-24 Peripherals/Black |
| AudioMax Headset-H1 White             |
+---------------------------------------+

Array Element Concatenation

SELECT 
    model,
    CONCAT_WS(', ', ARRAY_TO_STRING(tags, ', ')) as tag_list
FROM products
WHERE tags IS NOT NULL;

Result:

+------------+------------------------------+
|   model    |           tag_list           |
+------------+------------------------------+
| Laptop-X1  | gaming, premium, lightweight |
| Mouse-M2   | wireless, ergonomic          |
| Tablet-T3  | budget, compact              |
| Headset-H1 | bluetooth, noise-canceling   |
+------------+------------------------------+

Dynamic SQL Generation

This example uses CONCAT_WS() to help build a SQL WHERE clause:

SELECT CONCAT_WS(
    ' AND ',
    'category IS NOT NULL',
    'brand IS NOT NULL',
    'color IS NOT NULL'
) as where_clause;

Result:

+------------------------------------------------------------------+
|                           where_clause                           |
+------------------------------------------------------------------+
| category IS NOT NULL AND brand IS NOT NULL AND color IS NOT NULL |
+------------------------------------------------------------------+

Custom Formatting with Conditions

Here’s one that uses CASE WHEN statements with the CONCAT_WS() function to return a different result depending on the outcome of the conditions:

SELECT 
    CONCAT_WS(
        ' - ',
        CASE WHEN category IS NOT NULL THEN UPPER(category) END,
        brand,
        CASE WHEN color IS NOT NULL THEN CONCAT('(', color, ')') END
    ) as formatted_name
FROM products;

Result:

+----------------------------------+
|          formatted_name          |
+----------------------------------+
| ELECTRONICS - TechCo - (Silver)  |
| ACCESSORIES - GearPro            |
| ELECTRONICS - (Black)            |
| PERIPHERALS - ViewTech - (Black) |
| AudioMax - (White)               |
+----------------------------------+

`CONCAT_WS()` vs Alternatives

As with many things in SQL, we can often achieve the same or similar results with different approaches. Here are two alternative methods that we can use to get a similar result to CONCAT_WS().

`CONCAT_WS()` vs `CONCAT()`

DuckDB also has a CONCAT() function, which can be used to concatenate multiple values. The main difference is that it doesn’t accept a separator as its first argument. Therefore, we need to include the separator in each position that we want it to appear:

SELECT 
    -- Using CONCAT_WS
    CONCAT_WS(' - ', category, brand, model) as concat_ws_version,

    -- Using CONCAT
    CONCAT(category, ' - ', brand, ' - ', model) as concat_version
FROM products;

Result:

+-------------------------------------+-------------------------------------+
|          concat_ws_version          |           concat_version            |
+-------------------------------------+-------------------------------------+
| Electronics - TechCo - Laptop-X1    | Electronics - TechCo - Laptop-X1    |
| Accessories - GearPro - Mouse-M2    | Accessories - GearPro - Mouse-M2    |
| Electronics - Tablet-T3             | Electronics -  - Tablet-T3          |
| Peripherals - ViewTech - Monitor-24 | Peripherals - ViewTech - Monitor-24 |
| AudioMax - Headset-H1               |  - AudioMax - Headset-H1            |
+-------------------------------------+-------------------------------------+

So, while both functions allow us to get a similar result, the CONCAT() function required us to specify the separator twice. If we were concatenating more values, then we’d need to specify the separator even more.

Also, you’ll notice that the CONCAT() version sometimes results in the separator appearing in places it probably shouldn’t. For example, we get two separators side by side in the third row. And the fifth row starts with a separator. This is due to NULL values. The CONCAT_WS() function is smart enough to know not to output the separator when it encounters a NULL value.

For these reasons, CONCAT_WS() is usually a better option than CONCAT() when we need to perform concatenation with a separator.

`CONCAT_WS()` vs Concatenation Operator (`||`)

The string concatenation operator (||) is even more verbose than the CONCAT() function:

SELECT 
    -- Using CONCAT_WS
    CONCAT_WS(', ', category, brand, model) as concat_ws_version,

    -- Using string concatenation operator
    category || ', ' || brand || ', ' || model as operator_version
FROM products;

Result:

+-----------------------------------+-----------------------------------+
|         concat_ws_version         |         operator_version          |
+-----------------------------------+-----------------------------------+
| Electronics, TechCo, Laptop-X1    | Electronics, TechCo, Laptop-X1    |
| Accessories, GearPro, Mouse-M2    | Accessories, GearPro, Mouse-M2    |
| Electronics, Tablet-T3            | null                              |
| Peripherals, ViewTech, Monitor-24 | Peripherals, ViewTech, Monitor-24 |
| AudioMax, Headset-H1              | null                              |
+-----------------------------------+-----------------------------------+

The concatenation operator behaves differently to both CONCAT() and CONCAT_WS() when it comes to NULL values. The concatenation operator simply outputs NULL if any of the strings are NULL.

This makes the CONCAT_WS() function the better option if you want to handle NULL values in a more intelligent way.

Empty Separator

Passing an empty string as the separator results in no separator being used in the output:

SELECT 
    CONCAT_WS('', category, brand, model) as no_separator
FROM products;

Result:

+-------------------------------+
|         no_separator          |
+-------------------------------+
| ElectronicsTechCoLaptop-X1    |
| AccessoriesGearProMouse-M2    |
| ElectronicsTablet-T3          |
| PeripheralsViewTechMonitor-24 |
| AudioMaxHeadset-H1            |
+-------------------------------+

This has the same effect as using the CONCAT() function without any separators.

NULL Separator

Passing NULL as the separator results in NULL being returned:

SELECT 
    CONCAT_WS(NULL, category, brand, model) as null_separator
FROM products;

Result:

+----------------+
| null_separator |
+----------------+
| null           |
| null           |
| null           |
| null           |
| null           |
+----------------+

Syntax

Sample Data

Basic Usage

NULL Handling

Empty String Handling

Examples of Use Cases

Building Hierarchical Paths

Creating CSV-Style Output

Formatting Display Names

Array Element Concatenation

Dynamic SQL Generation

Custom Formatting with Conditions

CONCAT_WS() vs Alternatives

CONCAT_WS() vs CONCAT()

CONCAT_WS() vs Concatenation Operator (||)

Empty Separator

NULL Separator

`CONCAT_WS()` vs Alternatives

`CONCAT_WS()` vs `CONCAT()`

`CONCAT_WS()` vs Concatenation Operator (`||`)