How the concat_ws() function works in SQLite?

SQLite added concat_ws() in version 3.44.0. It joins non-NULL value arguments with the first argument as the separator; a NULL separator returns NULL. Check the exact SQLite version used by your application: SQLite 3.44.0 through 3.50.1 incorrectly omitted empty-string arguments, and 3.50.2 fixed that bug. SQLiz’s browser playground embeds SQLite 3.49.1, so its result for empty strings differs from current SQLite. See the official core function reference and 3.50.2 release notes.

Understanding the concat_ws() Basics

The concat_ws() function follows a simple but powerful pattern: it takes a separator as its first argument, then any number of additional string arguments to join together. The magic happens in how it handles those arguments:

SELECT concat_ws('-', '2023', '07', '15');  -- Returns '2023-07-15'
SELECT concat_ws(', ', 'John', NULL, 'Doe');  -- Returns 'John, Doe'

The second example skips the NULL value, so no extra separator appears in its place. For a comparison with other database engines, see SQL string concatenation by database.

Formatting Real-World Data

Where concat_ws() truly shines is in formatting structured data like addresses or personal names where some components might be missing:

SELECT concat_ws(' ', first_name, middle_name, last_name) AS full_name
FROM customers;

SELECT concat_ws(', ',
    NULLIF(address_line1, ''),
    NULLIF(address_line2, ''),
    NULLIF(city, ''),
    NULLIF(postal_code, '')
) AS full_address
FROM user_profiles;

The second example combines NULLIF() with concat_ws() to handle both NULL values and empty strings elegantly.

Building CSV and Delimited Output

Creating CSV-style output becomes trivial with concat_ws(), especially when dealing with tables where some columns might be NULL:

SELECT concat_ws('|',
    user_id,
    coalesce(email, 'no-email'),
    strftime('%Y-%m-%d', signup_date)
) AS csv_line
FROM users;

This produces clean, pipe-delimited output even when email addresses are missing, with a sensible default value in place.

Generating Readable Labels and Tags

You can use concat_ws() to create human-friendly labels from multiple data points:

SELECT
    product_id,
    concat_ws(' - ',
        product_name,
        CASE WHEN discontinued = 1 THEN 'DISCONTINUED' END,
        CASE WHEN stock_quantity < 10 THEN 'LOW STOCK' END
    ) AS product_label
FROM inventory;

This creates informative labels that only show status messages when relevant, separated by clear visual markers.

Nested concat_ws() for Complex Formatting

For more advanced formatting needs, you can nest concat_ws() calls to create hierarchical structures:

SELECT concat_ws(': ',
    concat_ws(' ', 'Customer', customer_id),
    concat_ws(', ',
        concat_ws(' ', first_name, last_name),
        phone_number
    )
) AS customer_header
FROM orders
JOIN customers USING (customer_id);

This produces output like: “Customer 12345: John Smith, 555-1234”

Handling Edge Cases and Special Scenarios

While concat_ws() is generally robust, there are some special cases worth noting:

-- What happens when only NULL values are provided?
SELECT concat_ws(', ', NULL, NULL, NULL);  -- Returns empty string

-- Using empty string as separator
SELECT concat_ws('', 'SQL', 'ite');  -- Works like regular concatenation

-- First argument must be a string (the separator)
SELECT concat_ws(NULL, 'A', 'B');  -- Returns NULL

-- Empty value arguments are retained in SQLite 3.50.2 and later
SELECT concat_ws('|', 'a', '', 'b', NULL, 'c');  -- Returns 'a||b|c' in 3.50.2+

In SQLite 3.44.0 through 3.50.1, the final query returns a|b|c because of the empty-string bug. SQLite 3.50.2 and later returns a||b|c.

Do not infer that concat_ws() short-circuits evaluation or is faster than another expression form from its NULL handling. Compare equivalent queries on the SQLite version and data used by your application if performance matters.

Conclusion

The concat_ws() function is one of those tools that seems simple at first glance but quickly becomes indispensable in your SQLite workflow. By elegantly solving the twin problems of separator management and NULL handling, it eliminates whole categories of string manipulation headaches. Whether you’re formatting reports, exporting data, or building display strings, concat_ws() helps you write cleaner, more maintainable SQL that behaves predictably even with incomplete data. Next time you find yourself writing complex CASE WHEN statements just to handle NULL values in string concatenation, remember that concat_ws() is probably the simpler solution you’re looking for.