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.
SELECT CONCAT_WS(' ', 'Ada', NULL, 'Lovelace') AS full_name; -- Ada Lovelace
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
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 |
-- 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.
||givesNULLwhen any part isNULL(except in Oracle).CONCATskips it in SQLite and PostgreSQL but not in MySQL. If you rely on a specific behavior, test it or useCOALESCE(). CONCAT_WSfor lists.CONCAT_WS(', ', street, city, country)never leaves a dangling comma whencityis missing.- Empty strings are not
NULL. PostgreSQL and MySQL keep them, soCONCAT_WS('-', 'a', '', 'c')givesa--cthere; SQLite skips them as well (the example above printsa-c). To get the same result everywhere, convert blanks first withNULLIF(col, ''). - Older SQLite (before 3.44) does not have these functions; use
||. - Do not confuse with aggregation.
CONCATjoins values across columns in one row; joining values across rows isGROUP_CONCAT()orSTRING_AGG(). - Dates and decimals convert using default formats; format them explicitly with
STRFTIME()orprintf(). - Not sargable. A filter on a concatenated expression cannot use indexes on the single columns.