CONCAT_WS()
SQL · String Functions
Theory
CONCAT_WS(separator, str1, str2, ...) joins strings and puts the separator between each pair, so you write the separator once instead of repeating it. CONCAT_WS(', ', 'Asha', 'IT', 'Jaipur') returns 'Asha, IT, Jaipur'. The name stands for 'concatenate with separator'.
Its most useful feature is how it treats NULLs: in MySQL, Postgres and SQL Server, NULL arguments are skipped entirely, together with their separator. So CONCAT_WS(', ', 'Meera', 'IT', NULL) gives 'Meera, IT' with no dangling comma. With plain || the same input would return NULL. Empty strings are not skipped, though, so '' still produces a double separator. In MySQL, a NULL separator makes the whole result NULL. Oracle and older SQLite builds do not have CONCAT_WS; there you emulate it with || and COALESCE(', ' || col, ''), as the diagram does.
Why we use it
Addresses, tags, CSV-style lines and full names often have optional parts. Building them with || means many COALESCE calls and stray separators. CONCAT_WS handles the separator and missing values in one call.
When to use it
Use CONCAT_WS when three or more values share one separator, especially if some can be NULL. Use CONCAT or || for two parts or mixed separators. For joining values from many rows, use STRING_AGG, GROUP_CONCAT or LISTAGG instead.
How it works
- Read the separator and the string arguments
- Is the separator NULL? Yes: MySQL returns NULL. No: Continue.
- Drop every NULL argument
- Join the remaining values with the separator between them
- Return the joined string (no dangling separator)
Syntax
CONCAT_WS(separator, str1, str2, ...)Example
SELECT CONCAT_WS(', ', name, department) FROM employees;Key points
- 'WS' stands for 'with separator'
- Skips NULL values, unlike manual || concatenation
- Empty strings are not skipped and still get a separator
- Missing in Oracle and older SQLite: emulate with || and COALESCE
- For values across rows use STRING_AGG / GROUP_CONCAT / LISTAGG