CONCATFunctions
Buy Me A Coffee

Concatenate two or more strings into a single string. Behavior with NULL values differs significantly between engines.

Joins two or more strings together. Syntax varies: MySQL uses CONCAT(), PostgreSQL/SQLite use double-pipe, SQL Server uses plus or CONCAT(). Watch for implicit type conversion and NULL propagation -- CONCAT(NULL, x) returns NULL in most engines.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported all CONCAT() ignores NULL arguments (treats them as empty string). CONCAT_WS(sep, ...) skips NULLs and adds a separator between non-null values.
PostgreSQL ✓ Supported all CONCAT() ignores NULLs (like MySQL). The || operator is the standard alternative but propagates NULLs — 'a' || NULL = NULL. Use CONCAT() or COALESCE() when NULL safety matters.
SQL Server ✓ Supported 2012 CONCAT() added in SQL Server 2012 and treats NULLs as empty strings. Before 2012, use + operator — but + propagates NULLs unless SET CONCAT_NULL_YIELDS_NULL OFF. CONCAT_WS() added in SQL Server 2017.
Oracle ✓ Supported all CONCAT() accepts exactly two arguments. For more than two strings, use the || operator or nest CONCAT() calls. In Oracle, NULL is treated as empty string in string concatenation (|| propagates, but CONCAT ignores).
SQLite ✓ Supported 3.44.0 CONCAT() added in SQLite 3.44.0 (Nov 2023). Before that, use the || operator exclusively. || propagates NULLs — use COALESCE() to guard against them.

Details

The biggest gotcha: the || operator propagates NULLs in PostgreSQL and SQLite (any NULL in the chain = NULL result), while CONCAT() in MySQL, SQL Server, and PostgreSQL treats NULLs as empty strings. Oracle's CONCAT() only takes two arguments. CONCAT_WS() (MySQL 2017+, SQL Server 2017+) is the cleanest option when you have a separator and want to skip NULLs automatically.

Standard Syntax

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

Version Support

MySQL: Since all PostgreSQL: Since all SQL Server: Since 2012 Oracle: Since all SQLite: Since 3.44.0

Per-Database Syntax & Notes

MySQL

CONCAT() ignores NULL arguments (treats them as empty string). CONCAT_WS(sep, ...) skips NULLs and adds a separator between non-null values.

SELECT CONCAT('Hello', ' ', 'World'); -- 'Hello World' SELECT CONCAT('a', NULL, 'b'); -- 'ab' (NULL silently ignored) SELECT CONCAT_WS(', ', 'Alice', NULL, 'Bob'); -- 'Alice, Bob'

PostgreSQL

CONCAT() ignores NULLs (like MySQL). The || operator is the standard alternative but propagates NULLs — 'a' || NULL = NULL. Use CONCAT() or COALESCE() when NULL safety matters.

SELECT CONCAT('Hello', ' ', 'World'); -- 'Hello World' SELECT 'Hello' || ' ' || 'World'; -- 'Hello World' SELECT 'a' || NULL; -- NULL SELECT CONCAT('a', NULL, 'b'); -- 'ab'

SQL Server

CONCAT() added in SQL Server 2012 and treats NULLs as empty strings. Before 2012, use + operator — but + propagates NULLs unless SET CONCAT_NULL_YIELDS_NULL OFF. CONCAT_WS() added in SQL Server 2017.

SELECT CONCAT('Hello', ' ', 'World'); -- 'Hello World' SELECT CONCAT('a', NULL, 'b'); -- 'ab' SELECT 'Hello' + ' ' + 'World'; -- 'Hello World' (pre-2012) SELECT CONCAT_WS(', ', 'Alice', NULL, 'Bob'); -- 'Alice, Bob' (2017+)

Oracle

CONCAT() accepts exactly two arguments. For more than two strings, use the || operator or nest CONCAT() calls. In Oracle, NULL is treated as empty string in string concatenation (|| propagates, but CONCAT ignores).

SELECT CONCAT('Hello', ' World') FROM DUAL; -- 'Hello World' SELECT 'Hello' || ' ' || 'World' FROM DUAL; -- 'Hello World' SELECT CONCAT(CONCAT('a', 'b'), 'c') FROM DUAL; -- 'abc'

SQLite

CONCAT() added in SQLite 3.44.0 (Nov 2023). Before that, use the || operator exclusively. || propagates NULLs — use COALESCE() to guard against them.

SELECT CONCAT('Hello', ' ', 'World'); -- 3.44.0+ SELECT 'Hello' || ' ' || 'World'; -- all versions SELECT COALESCE(name, '') || ' ' || COALESCE(suffix, '') FROM tbl;