How to Use the CONCAT_WS() Function in MySQL

08/05/2021

Contents

In this article, you will learn how to use the CONCAT_WS() function in MySQL.

Using the CONCAT_WS() function in MySQL

The CONCAT_WS() function in MySQL is used to concatenate two or more strings with a separator. The “WS” in the function name stands for “with separator”. This function is useful when you want to concatenate strings with a specific separator between them.

Syntax

Here is the syntax for using the CONCAT_WS() function in MySQL:

CONCAT_WS(separator, str1, str2, ...)
 
  • separator – The separator to use between the strings.
  • str1, str2, … – The strings to be concatenated.

Examples

Here are some examples of how to use the CONCAT_WS() function in MySQL:

Using CONCAT_WS() to concatenate strings with a separator

SELECT CONCAT_WS(',', 'apple', 'banana', 'orange');

Output:

apple,banana,orange

In this example, the CONCAT_WS() function is used to concatenate the strings ‘apple’, ‘banana’, and ‘orange’ with a comma (‘,’) separator between them.

Using CONCAT_WS() to concatenate strings with null values

SSELECT CONCAT_WS('-', 'apple', NULL, 'orange');

Output:

apple-orange

In this example, the CONCAT_WS() function is used to concatenate the strings ‘apple’, NULL (which is treated as an empty string), and ‘orange’ with a hyphen (‘-‘) separator between them.

Using CONCAT_WS() to concatenate strings with multiple separators

SELECT CONCAT_WS(',', 'apple', NULL, 'banana', '', 'orange');

Output:

apple,banana,orange

In this example, the CONCAT_WS() function is used to concatenate the strings ‘apple’, NULL, ‘banana’, an empty string, and ‘orange’ with a comma (‘,’) separator between them. The NULL value and empty string are treated as the same and are not included in the result.

#MySQL