How the concat() function works in SQLite?

SQLite added the scalar concat() function in version 3.44.0. It joins the text representations of its non-NULL arguments; NULL arguments are treated as empty strings, and an all-NULL argument list returns an empty string. The || operator also concatenates values after converting them to text, but returns NULL if either operand is NULL. See SQLite’s core function reference, expression documentation, and 3.44.0 release notes.

The Simple Power of concat()

At its core, the concat() function takes any number of arguments and combines them into one string. What makes it special is its forgiving nature with different data types and its handling of NULL values:

SELECT concat('SQL', 'ite');  -- Returns 'SQLite'
SELECT concat(42, ' is the answer');  -- Returns '42 is the answer'
SELECT concat('Date: ', date('2026-07-15'));  -- Returns 'Date: 2026-07-15'

The values are converted to their text representations before concatenation. The fixed date literal keeps the output stable when you run this example.

Handling NULL Values Gracefully

One of concat()’s most useful features is its NULL handling behavior. Unlike the || concatenation operator which returns NULL if any operand is NULL, concat() treats NULL as an empty string:

-- Using || operator
SELECT 'Hello' || NULL || 'World';  -- Returns NULL

-- Using concat()
SELECT concat('Hello', NULL, 'World');  -- Returns 'HelloWorld'

This behavior makes concat() invaluable when working with potentially incomplete data where NULL values might appear.

Combining Columns for Readable Output

Real-world data often needs recombination for human-readable display. concat() excels at this:

SELECT concat(first_name, ' ', last_name) AS full_name
FROM employees;

SELECT concat(
    product_name,
    ' (',
    product_code,
    ') - $',
    price
) AS product_label
FROM inventory;

The second example shows how you can mix column values with static text for professional-looking labels.

Formatting Complex Strings with Nested concat()

For more sophisticated formatting, you can nest concat() functions:

SELECT concat(
    'Order #',
    order_id,
    concat(' (', order_date, ')'),
    ' - Status: ',
    order_status
) AS order_summary
FROM orders;

This produces output like: “Order #1045 (2023-07-15) - Status: Shipped”

Type Conversion Magic

concat() automatically handles type conversion, saving you from manual casting in many cases:

SELECT concat('User ', user_id, ' created on ', created_at) AS user_info
FROM users;

SELECT concat('The price is $', price, ' with ', discount_percent, '% off')
FROM products;

Numbers, dates, and other non-string types are seamlessly converted to strings.

When to Use concat() vs ||

While both concat() and the || operator combine strings, they have important differences:

-- concat() treats NULL as empty string
SELECT concat('A', NULL, 'B');  -- 'AB'

-- || returns NULL if any operand is NULL
SELECT 'A' || NULL || 'B';  -- NULL

-- concat() automatically converts types
SELECT concat(1, 2, 3);  -- '123'

-- || also concatenates the text representations of values
SELECT 1 || 2 || 3;  -- '123'

Use concat() when NULL arguments should be treated as empty strings. Use || when a NULL operand should make the full result NULL. Both forms accept numeric operands in SQLite; an explicit cast is not required just to concatenate their text representations.

Conclusion

The function is available only in SQLite 3.44.0 and later. SQLiz’s browser playground embeds SQLite 3.49.1, which supports concat(). For behavior across MySQL, MariaDB, PostgreSQL, SQLite, SQL Server, and Oracle, see SQL string concatenation by database.