MySQL CONCAT_WS() Function

The CONCAT_WS() function in MySQL is used to concatenate (combine) two or more strings or expressions into a single string, with a specified separator inserted between each value. This function is especially useful when you want to format combined strings with consistent separators.

Syntax

CONCAT_WS(separator, expression1, expression2, expression3, ...)

Parameters

Parameter	Description
separator	Required. The string to insert between each of the expressions. If the separator is `NULL`, the function returns `NULL`.
expression1, ...	Required. The strings or expressions to concatenate. Any `NULL` values in the expressions are ignored.

Definition and Use

Purpose: Concatenates multiple strings, inserting a specified separator between them.
Behavior: Skips any NULL values in the expressions, making it more flexible than CONCAT().
Special Note: The separator is not added at the beginning or end, only between the expressions.

Usage Example 1: Concatenating with a Separator

To combine strings with a hyphen (-) as a separator:

SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!") AS ConcatenatedString;

Output:

ConcatenatedString
SQL-Tutorial-is-fun!

Usage Example 2: Concatenating Column Values

To combine values from multiple columns into a single "Address" column with spaces as separators:

SELECT CONCAT_WS(" ", Address, PostalCode, City) AS Address
FROM Customers;

Output Explanation:

Combines values from the Address, PostalCode, and City columns, separated by spaces.

Technical Details

Availability: Available from MySQL 4.0 onwards.
Return Type: Returns a single string containing the concatenated expressions with the separator.

Applications

Formatting Data: Generate formatted strings for display or export (e.g., full addresses, names).
Skipping NULL Values: Handle nullable fields gracefully without introducing NULL in the output.
Custom Separators: Create strings with consistent separators, such as commas, spaces, or dashes.

Example Outputs

Suppose the Customers table contains the following data:

Address	PostalCode	City
123 Main St	12345	New York
456 Elm St	67890	Chicago
NULL	54321	Boston

After running the query:

SELECT CONCAT_WS(" ", Address, PostalCode, City) AS Address
FROM Customers;

The result will be:

Address
123 Main St 12345 New York
456 Elm St 67890 Chicago
54321 Boston

Key Notes

If the separator is NULL, the entire result will be NULL.
Unlike CONCAT(), CONCAT_WS() automatically ignores NULL values, making it ideal for concatenating columns where some may have missing values.

The CONCAT_WS() function is a versatile tool for creating well-formatted, dynamic strings in MySQL queries, providing control over separators and handling nullable data gracefully.