CONCAT_WS()

Syntax

How it works

CONCAT_WS() stands for "concatenate with separator". Unlike plain CONCAT(), you pass the separator once as the first argument and the function places it between every remaining value for you. This makes it the cleanest way to build comma separated lines, full addresses and full names without sprinkling the separator by hand.

The key behaviour is that CONCAT_WS() SKIPS NULL arguments. If a value is NULL, it is left out and no extra separator is produced for it, so you never end up with a doubled or trailing separator. This is different from CONCAT(), which in MySQL simply treats NULL as an empty string, and from the || operator, which in standard SQL turns the whole expression NULL if any part is NULL.

Because it ignores missing pieces, CONCAT_WS() is ideal for optional fields such as a middle name or a second address line. It is available in MySQL, PostgreSQL and SQL Server (2017 and later). Note that SQLite does not have CONCAT_WS(); there you must fall back to the || operator with manual NULL handling.

Examples

Join address parts into one line

SELECT CONCAT_WS(', ', street, city, country) AS full_address
FROM customers;

full_address
----------------------------
12 Palm St, Dubai, UAE
8 Nile Rd, Cairo, EGY

Build a full name that skips a missing middle name

-- middle_name is NULL for many people
SELECT CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name
FROM people;

full_name
-------------------
Aisha Khan
John Michael Doe

Build a CSV row from several columns

SELECT CONCAT_WS(',', id, name, email, country) AS csv_line
FROM customers;

csv_line
----------------------------------
1,Aisha Khan,aisha@x.com,UAE
2,John Doe,john@x.com,EGY

NULL values are dropped, no doubled separator

-- addr2 is NULL, so it is skipped cleanly
SELECT CONCAT_WS(' | ', addr1, addr2, city) AS label
FROM sites;

label
----------------------
Unit 5 | Dubai
Unit 9 | Floor 2 | Doha

Separator applies only between values

SELECT CONCAT_WS('-', '2026', '07', '04') AS iso_date;

iso_date
----------
2026-07-04

Common mistakes

-- Expecting NULL to show as an empty slot
-- between the separators
SELECT CONCAT_WS(', ', city, region, country)
FROM addresses;  -- region is NULL

-- If you truly want a placeholder, coalesce first
SELECT CONCAT_WS(', ', city, COALESCE(region, 'N/A'), country)
FROM addresses;

-- Treating the first argument as data
SELECT CONCAT_WS(first_name, last_name, email)
FROM users;

-- The first argument is the separator
SELECT CONCAT_WS(' ', first_name, last_name, email)
FROM users;

-- Using CONCAT_WS in SQLite
SELECT CONCAT_WS('-', a, b) FROM t;

-- SQLite has no CONCAT_WS; use || instead
SELECT a || '-' || b FROM t;

Performance

CONCAT_WS() is a lightweight scalar function evaluated once per row, so on its own it is cheap. The cost you should watch is applying it to columns you then filter on: wrapping columns in CONCAT_WS() inside a WHERE clause prevents the optimiser from using an index, because the engine must build the string for every row before comparing.

When you only need a display value, compute CONCAT_WS() in the SELECT list rather than in WHERE or JOIN conditions. If you must search on a joined string, store it in a persisted computed column or a generated column and index that instead of building it on the fly for every query.

For very large exports, building CSV lines with CONCAT_WS() in SQL is fine, but pushing millions of rows through string concatenation adds CPU work; measure whether formatting in the application layer is cheaper for your workload.

Interview questions

SELECT CONCAT_WS('-', 'a', NULL, 'b') AS r;  -- 'a-b'

SELECT CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name
FROM people;