CONCAT()
SQL · String Functions
Theory
CONCAT joins two or more strings into one, in the order given: CONCAT(first_name, ' ', last_name) turns 'Asha' and 'Rao' into 'Asha Rao'. Numbers are converted to text automatically in most databases, so CONCAT('ORD-', 101) gives 'ORD-101'. The SQL-standard way to join strings is the || operator, which Postgres, Oracle and SQLite support; SQL Server uses + (and also has CONCAT), and MySQL has CONCAT (there || means OR unless a mode is changed).
The big difference between dialects is NULL handling. With ||, any NULL makes the whole result NULL: 'Meera' || ' ' || NULL is NULL. Postgres and SQL Server CONCAT treat NULL as an empty string, but MySQL CONCAT returns NULL if any argument is NULL. Oracle is the odd one out, treating NULL as empty even with ||. The safe, portable habit is to wrap columns that may be NULL in COALESCE(col, '').
Why we use it
Data is often stored in parts (first and last name, house number and street, code prefix and number) but needs to be shown or matched as one string. CONCAT builds full names, labels, keys and messages directly in the query.
When to use it
Use CONCAT or || whenever you build display text or composite keys. When several parts share the same separator, CONCAT_WS is cleaner. Always think about NULL parts: wrap them in COALESCE or the result may silently become NULL.
How it works
- Take the list of string arguments
- Convert non-text values (numbers, dates) to text
- Is any argument NULL? Yes: Result NULL, or NULL treated as ''. No: Use the value as is.
- Append each piece in order
- Return the joined string
Syntax
CONCAT(str1, str2, ...)Example
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;Key points
- Standard operator is ||; SQL Server uses + and also has CONCAT()
- MySQL CONCAT returns NULL if any argument is NULL
- Postgres and SQL Server CONCAT treat NULL as an empty string
- With ||, one NULL makes the whole result NULL: wrap with COALESCE
- Newer SQLite builds add concat(); older ones only support ||