SQL CONCAT Function: Combine Strings (All Databases)

Overview

CONCAT joins two or more strings into one. Every database supports it, but the syntax varies — especially for NULL handling and separators.

MySQL

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

-- CONCAT_WS (with separator):
SELECT CONCAT_WS(', ', city, state, country) AS address
FROM customers;
-- Result: 'New York, NY, USA'

-- NULL handling: CONCAT returns NULL if ANY arg is NULL
SELECT CONCAT('Hello', NULL);  -- Returns NULL
-- Use IFNULL to handle:
SELECT CONCAT(first_name, ' ', IFNULL(last_name, '')) FROM users;

PostgreSQL

-- || operator (idiomatic):
SELECT first_name || ' ' || last_name AS full_name
FROM users;

-- CONCAT function (NULL-safe — treats NULL as empty string):
SELECT CONCAT(first_name, ' ', last_name) FROM users;

-- CONCAT_WS:
SELECT CONCAT_WS(', ', city, state, country) FROM customers;

-- || with NULL returns NULL:
SELECT 'Hello' || NULL;  -- Returns NULL
-- CONCAT ignores NULL:
SELECT CONCAT('Hello', NULL);  -- Returns 'Hello'

SQL Server

-- + operator:
SELECT first_name + ' ' + last_name AS full_name
FROM users;

-- CONCAT function (NULL-safe, SQL Server 2012+):
SELECT CONCAT(first_name, ' ', last_name) FROM users;

-- CONCAT_WS (SQL Server 2017+):
SELECT CONCAT_WS(', ', city, state, country) FROM customers;

-- + returns NULL if any part is NULL:
SELECT 'Hello' + NULL;  -- Returns NULL
-- CONCAT treats NULL as empty:
SELECT CONCAT('Hello', NULL);  -- Returns 'Hello'

Oracle

-- || operator:
SELECT first_name || ' ' || last_name AS full_name
FROM users;

-- CONCAT function (only 2 arguments):
SELECT CONCAT(CONCAT(first_name, ' '), last_name) FROM users;

-- || with NULL (Oracle treats NULL as empty string in ||):
SELECT 'Hello' || NULL FROM DUAL;  -- Returns 'Hello'

Skip the Syntax Lookup

Instead of memorizing CONCAT syntax for each database, describe what you need in plain English and let AI2SQL generate the correct query.

Frequently Asked Questions