An Overview of the CONCAT() Function in DuckDB

String concatenation is a common operation in database queries, and many database management systems (DBMSs) provide at least one or two ways to concatenate strings. DuckDB is no exception.

One option for concatenating strings in DuckDB is with the CONCAT() function. This function provides a robust and NULL-safe way to combine strings.

Unlike the concatenation operator (||), CONCAT() handles NULL values gracefully and provides a cleaner syntax for combining multiple strings.

In this article, we’ll explore everything from basic usage to advanced techniques and best practices.

Syntax

Let’s start with the syntax:

CONCAT(string, ...)

What this means is that we can pass one or more strings to the function, which means we can concatenate as many strings as we like with one function call. This is in comparison with the concatenation operator (||), which could require us to use the operator many times, depending on how many strings we want to concatenate.

Sample Data

The examples on this page will use the following data:

-- Create a sample table
CREATE TABLE users (
    id INTEGER,
    first_name VARCHAR,
    last_name VARCHAR,
    title VARCHAR,
    department VARCHAR
);

-- Insert sample data
INSERT INTO users VALUES
    (1, 'Blake', 'Wigley', 'Senior Developer', 'Engineering'),
    (2, 'Britney', 'Gaga', NULL, 'Marketing'),
    (3, 'Bono', NULL, 'Sales Rep', 'Sales'),
    (4, 'Ahmed', 'Slater', 'Manager', NULL),
    (5, NULL, 'Farnsworth', 'Analyst', 'Finance');

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() handles them.

Basic `CONCAT()` Usage

Here’s an example of using the CONCAT() function to concatenate the first name column with the last name column:

SELECT CONCAT(first_name, ' ', last_name) as full_name
FROM users;

Result:

+--------------+
|  full_name   |
+--------------+
| Blake Wigley |
| Britney Gaga |
| Bono         |
| Ahmed Slater |
|  Farnsworth  |
+--------------+

We can see that the first name and last name of each employee is output in a single column, due to the concatenation operation.

To highlight this, here it is with the first name and last names in separate columns:

SELECT 
    first_name,
    last_name,
    CONCAT(first_name, ' ', last_name) as full_name
FROM users;

Result:

+------------+------------+--------------+
| first_name | last_name  |  full_name   |
+------------+------------+--------------+
| Blake      | Wigley     | Blake Wigley |
| Britney    | Gaga       | Britney Gaga |
| Bono       | null       | Bono         |
| Ahmed      | Slater     | Ahmed Slater |
| null       | Farnsworth |  Farnsworth  |
+------------+------------+--------------+

NULL Handling

We can see from the previous example that CONCAT() automatically skips NULL values, which is one of its main advantages over the concatenation operator:

SELECT 
    first_name,
    last_name,
    CONCAT(first_name, ' ', last_name) as with_concat,
    first_name || ' ' || last_name as with_operator
FROM users;

Result:

+--------------+---------------+
| with_concat  | with_operator |
+--------------+---------------+
| Blake Wigley | Blake Wigley  |
| Britney Gaga | Britney Gaga  |
| Bono         | null          |
| Ahmed Slater | Ahmed Slater  |
|  Farnsworth  | null          |
+--------------+---------------+

Here, we can see that there’s a difference between how CONCAT() and the concatenation operator handle NULL values.

Space vs Empty String vs NULL

It’s important to understand the difference between a space and an empty string:

SELECT 
    CONCAT('Red', NULL, 'Widget') as null_example,
    CONCAT('Red', '', 'Widget') as empty_string_example,
    CONCAT('Red', ' ', 'Widget') as space_example;

Result:

+--------------+----------------------+---------------+
| null_example | empty_string_example | space_example |
+--------------+----------------------+---------------+
| RedWidget    | RedWidget            | Red Widget    |
+--------------+----------------------+---------------+

We can see that the empty string had the same effect that the NULL value had. But when using a space character, the space is preserved.

Multiple Arguments

The CONCAT() function can handle any number of arguments:

SELECT CONCAT(
    'ID: ',
    id::VARCHAR,
    ' - ',
    first_name,
    ' ',
    last_name,
    ' (',
    department,
    ')'
) as user_detail
FROM users;

Result:

+------------------------------------+
|            user_detail             |
+------------------------------------+
| ID: 1 - Blake Wigley (Engineering) |
| ID: 2 - Britney Gaga (Marketing)   |
| ID: 3 - Bono  (Sales)              |
| ID: 4 - Ahmed Slater ()            |
| ID: 5 -  Farnsworth (Finance)      |
+------------------------------------+

Example Use Cases

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

Building Email Addresses

SELECT 
    CONCAT(
        LOWER(first_name),
        '.',
        LOWER(last_name),
        '@company.com'
    ) as email_address
FROM users
WHERE first_name IS NOT NULL 
AND last_name IS NOT NULL;

Result:

+--------------------------+
|      email_address       |
+--------------------------+
| [email protected] |
| [email protected] |
| [email protected] |
+--------------------------+

Creating Full Titles

SELECT 
    CONCAT(
        title,
        ' ',
        first_name,
        ' ',
        last_name,
        ', ',
        department
    ) as professional_title
FROM users;

Result:

+--------------------------------------------+
|             professional_title             |
+--------------------------------------------+
| Senior Developer Blake Wigley, Engineering |
|  Britney Gaga, Marketing                   |
| Sales Rep Bono , Sales                     |
| Manager Ahmed Slater,                      |
| Analyst  Farnsworth, Finance               |
+--------------------------------------------+

Building File Paths

SELECT 
    CONCAT(
        '/users/',
        id::VARCHAR,
        '/',
        LOWER(first_name),
        '_',
        LOWER(last_name),
        '.pdf'
    ) as document_path
FROM users
WHERE first_name IS NOT NULL 
AND last_name IS NOT NULL;

Result:

+---------------------------+
|       document_path       |
+---------------------------+
| /users/1/blake_wigley.pdf |
| /users/2/britney_gaga.pdf |
| /users/4/ahmed_slater.pdf |
+---------------------------+

Dynamic SQL Generation

-- Building WHERE clauses
SELECT CONCAT(
    'SELECT * FROM users WHERE ',
    'department = ''',
    department,
    ''' AND title = ''',
    title,
    ''''
) as dynamic_query
FROM users
WHERE department IS NOT NULL 
AND title IS NOT NULL
LIMIT 1;

Result:

+-------------------------------------------------------------------------------------+
|                                    dynamic_query                                    |
+-------------------------------------------------------------------------------------+
| SELECT * FROM users WHERE department = 'Engineering' AND title = 'Senior Developer' |
+-------------------------------------------------------------------------------------+

Combining with Other Functions

We can use CONCAT() in combination with other functions to achieve a desired outcome.

For example:

SELECT 
    CONCAT(
        UPPER(LEFT(first_name, 1)),
        '. ',
        UPPER(last_name)
    ) as formatted_name
FROM users
WHERE first_name IS NOT NULL 
AND last_name IS NOT NULL;

Result:

+----------------+
| formatted_name |
+----------------+
| B. WIGLEY      |
| B. GAGA        |
| A. SLATER      |
+----------------+

Here we used the UPPER() function to convert everything to uppercase, as well as the LEFT() function to get each employee’s first initial.

`CONCAT()` vs. Alternative Methods

The CONCAT() function isn’t the only way to perform concatenation in DuckDB. We’ve already seen the concatenation operator in action, and there’s also a CONCAT_WS() function that can be useful when concatenating multiple values with the same separator.

`CONCAT()` vs. `||` Operator

We saw previously how the CONCAT() function differs from the concatenation operator with regards to how it handles NULL values. As with many things in SQL, there are often multiple ways of achieving a desired outcome, and it’s possible to construct a query with the concatenation operator that produces the same result as the CONCAT() function.

Here’s an example:

-- Compare approaches
SELECT 
    -- CONCAT() method (NULL-safe)
    CONCAT(first_name, ' ', last_name) as concat_method,

    -- || operator (not NULL-safe)
    first_name || ' ' || last_name as operator_method,

    -- || operator with COALESCE (NULL-safe but verbose)
    COALESCE(first_name, '') || ' ' || COALESCE(last_name, '') as safe_operator_method
FROM users;

Result:

+---------------+-----------------+----------------------+
| concat_method | operator_method | safe_operator_method |
+---------------+-----------------+----------------------+
| Blake Wigley  | Blake Wigley    | Blake Wigley         |
| Britney Gaga  | Britney Gaga    | Britney Gaga         |
| Bono          | null            | Bono                 |
| Ahmed Slater  | Ahmed Slater    | Ahmed Slater         |
|  Farnsworth   | null            |  Farnsworth          |
+---------------+-----------------+----------------------+

In this example, we can look at the first two columns to see how the CONCAT() function and the concatenation operator handle NULL values differently. The third column however, uses the concatenation operator in conjunction with the COALESCE() function in order to deal with NULL values in the same way that CONCAT() does.

While this technique produces the same output, it’s a lot more verbose, and possibly not as efficient as simply using the CONCAT() function.

`CONCAT()` vs. `CONCAT_WS()`

The CONCAT_WS() function is very similar to the CONCAT() function, except that it accepts a separator as its first argument. This separator will separate each value that’s concatenated.

SELECT 
    -- Standard CONCAT
    CONCAT(department, ' - ', title, ' - ', first_name, ' - ', last_name) as concat_version,

    -- CONCAT_WS (more concise for consistent separators)
    CONCAT_WS(' - ', department, title, first_name, last_name) as concat_ws_version
FROM users;

Output:

+-------------------------------------------------+-------------------------------------------------+
|                 concat_version                  |                concat_ws_version                |
+-------------------------------------------------+-------------------------------------------------+
| Engineering - Senior Developer - Blake - Wigley | Engineering - Senior Developer - Blake - Wigley |
| Marketing -  - Britney - Gaga                   | Marketing - Britney - Gaga                      |
| Sales - Sales Rep - Bono -                      | Sales - Sales Rep - Bono                        |
|  - Manager - Ahmed - Slater                     | Manager - Ahmed - Slater                        |
| Finance - Analyst -  - Farnsworth               | Finance - Analyst - Farnsworth                  |
+-------------------------------------------------+-------------------------------------------------+

We can see that CONCAT_WS() can achieve the desired result with less code and repetition. We can also see that it deals with NULL values better, in that it doesn’t output the separator for NULL values. For example, in the left column (using the CONCAT() function) two separators have been concatenated in the second row due to there being a NULL value. A similar situation has arisen in the third row, with a trailing separator (due to no last name), and again in the fourth row, with a leading separator (due to the department being NULL).

Conclusion

DuckDB’s CONCAT() function offers:

Automatic NULL handling
Support for multiple arguments
Clean syntax for string combination

Tips:

Use CONCAT() when dealing with potential NULL values
Consider CONCAT_WS() for consistent separators
Use appropriate NULL handling strategies for your use case

Remember that CONCAT() is particularly useful when dealing with NULL values and multiple string combinations, making it a great option when performing string manipulation in DuckDB.

Syntax

Sample Data

Basic CONCAT() Usage

NULL Handling

Space vs Empty String vs NULL

Multiple Arguments

Example Use Cases

Building Email Addresses

Creating Full Titles

Building File Paths

Dynamic SQL Generation

Combining with Other Functions

CONCAT() vs. Alternative Methods

CONCAT() vs. || Operator

CONCAT() vs. CONCAT_WS()

Conclusion

Basic `CONCAT()` Usage

`CONCAT()` vs. Alternative Methods

`CONCAT()` vs. `||` Operator

`CONCAT()` vs. `CONCAT_WS()`