Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Scalar Functions

SQL CONCAT() and CONCAT_WS() functions

Reference for SQL CONCAT() and CONCAT_WS(): join several values with or without a separator, NULL handling in each database and the || operator alternative.

CONCAT(a, b, ...) joins any number of values into one string. CONCAT_WS(separator, a, b, ...) ("with separator") does the same but puts a separator between the values and skips NULLs, which makes it ideal for building names and addresses where some parts are missing.

These functions are not in the SQL standard, which uses the || operator. Still, they exist in SQLite (3.44 and later), PostgreSQL, MySQL and SQL Server, with differences in how they treat NULL.

SQL
SELECT CONCAT_WS(' ', 'Ada', NULL, 'Lovelace') AS full_name;   -- Ada Lovelace

Try it

SQL NULL handling: CONCAT, CONCAT_WS and ||

Press Run (or Ctrl + Enter) to see the result here.

SQL Building labels from table columns ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Optional parts disappear cleanly ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
CONCAT(value1, value2 [, value3 ...])
CONCAT_WS(separator, value1, value2 [, value3 ...])
Name Description
value1, value2, ... The values to join. Numbers are converted to text.
separator The text placed between the values. If it is NULL, the result is NULL.

Returns: the joined string. Never NULL from CONCAT in SQLite, PostgreSQL and SQL Server, even if all arguments are NULL (the result is an empty string).

Dialect differences

Database CONCAT CONCAT_WS NULL arguments
SQLite (3.44+) any number of values (at least one) yes skipped by both
PostgreSQL any number yes skipped by both
MySQL any number yes CONCAT returns NULL if any argument is NULL; CONCAT_WS skips them
SQL Server 2 to 254 arguments (2012+) 2017+ treated as empty by CONCAT; skipped by CONCAT_WS
Oracle exactly two arguments none NULL acts like an empty string
SQL
-- MySQL: one NULL makes the whole result NULL
SELECT CONCAT('a', NULL, 'c');          -- NULL
SELECT CONCAT_WS('-', 'a', NULL, 'c');  -- a-c

-- Oracle: nest CONCAT or use ||
SELECT first_name || ' ' || last_name FROM employees;

Usage notes

  • Choose by NULL behavior. || gives NULL when any part is NULL (except in Oracle). CONCAT skips it in SQLite and PostgreSQL but not in MySQL. If you rely on a specific behavior, test it or use COALESCE().
  • CONCAT_WS for lists. CONCAT_WS(', ', street, city, country) never leaves a dangling comma when city is missing.
  • Empty strings are not NULL. PostgreSQL and MySQL keep them, so CONCAT_WS('-', 'a', '', 'c') gives a--c there; SQLite skips them as well (the example above prints a-c). To get the same result everywhere, convert blanks first with NULLIF(col, '').
  • Older SQLite (before 3.44) does not have these functions; use ||.
  • Do not confuse with aggregation. CONCAT joins values across columns in one row; joining values across rows is GROUP_CONCAT() or STRING_AGG().
  • Dates and decimals convert using default formats; format them explicitly with STRFTIME() or printf().
  • Not sargable. A filter on a concatenated expression cannot use indexes on the single columns.