How to Use CONCAT_WS in MySQL: Syntax and Examples

In MySQL, CONCAT_WS(separator, str1, str2, ...) concatenates strings using the specified separator between each string. It skips any NULL values, making it useful for combining columns with a delimiter.

Syntax

The CONCAT_WS function syntax is:

• separator: The string used to separate the concatenated values.
• str1, str2, ...: The strings or columns to concatenate.

It returns a single string with the values joined by the separator, ignoring any NULL values.

sql

CONCAT_WS(separator, str1, str2, ...)

Example

This example shows how to concatenate first and last names with a space separator, skipping any NULL values:

sql

SELECT CONCAT_WS(' ', 'John', 'Doe') AS full_name;

SELECT CONCAT_WS('-', '2024', '04', '27') AS date_string;

-- Example with NULL value
SELECT CONCAT_WS(', ', 'apple', NULL, 'banana') AS fruits;

Output

full_name
John Doe

date_string
2024-04-27

fruits
apple, banana

Common Pitfalls

Common mistakes when using CONCAT_WS include:

• Using CONCAT instead of CONCAT_WS when a separator is needed.
• Passing NULL as the separator, which returns NULL for the whole result.
• Expecting NULL values to appear as empty strings; CONCAT_WS skips them instead.

sql

/* Wrong: NULL separator returns NULL */
SELECT CONCAT_WS(NULL, 'a', 'b');

/* Right: Use a valid separator */
SELECT CONCAT_WS('-', 'a', 'b');

Output

NULL

a-b

Quick Reference

Parameter	Description
separator	String used to separate the concatenated values
str1, str2, ...	Strings or columns to concatenate
NULL values	Skipped and not included in the result
Return	Concatenated string with separator between values

Key Takeaways

Use CONCAT_WS to join strings with a separator while skipping NULLs.

The first argument is the separator string used between values.

NULL separator causes the entire result to be NULL.

CONCAT_WS is useful for combining columns with delimiters cleanly.

It differs from CONCAT by automatically adding separators and ignoring NULLs.