MySQL CONCAT_WS() Function [With Easy Examples]

By /
CONCAT WS() Function

In a previous tutorial, we studied the MySQL CONCAT() function. Building on that, this tutorial will focus on the MySQL CONCAT_WS() function.

The MySQL CONCAT_WS() function is used to concatenate or combine two or more expressions with a separator.

This separator is added between the expressions and then we get our concatenated string as our result. It is important to note that if the separator is NULL, then the result is NULL. Just like CONCAT(), we use CONCAT_WS() with the SELECT statement.

Syntax of MySQL CONCAT_WS()

CONCAT_WS(separator, expression1, expression2, expression3,...)Code language: SQL (Structured Query Language) (sql)

Where the separator is the string or character which you want between the expressions.

Difference between CONCAT() and CONCAT_WS()

Suppose you have the following three strings – “I am”, “12 years”, “old”. Now let us concatenate them using the CONCAT() function and use the alias ConcatenatedString to refer to it in the output. So, our query is,

SELECT CONCAT(“I am”, “12 years”, “old”) AS ConcatenatedString;Code language: SQL (Structured Query Language) (sql)

Our output is,

Concat Example

“I am12 yearsold” as output is hardly readable and beats the purpose. CONCAT() does NOT add spaces between two expressions unless you have them in your expressions.

Here’s where CONCAT_WS() comes into the picture. Let’s take the same example and specify our separator is a whitespace character (“ “). So our query is,

SELECT CONCAT_WS(“ “, “I am”, “12 years”, “old”) AS ConcatenatedString;Code language: SQL (Structured Query Language) (sql)

And our output is,

Concat Ws Example

Now that’s more readable, isn’t it?

Examples of MySQL CONCAT_WS()

Now that you understand the differences between both the functions, let’s go over a few examples to see how the MySQL CONCAT_WS() function works.

1. Simple Example of CONCAT_WS()

Let us take a simple example to start with. Suppose you have the following two strings – “support” and “gmail.com” that need to be concatenated such that the output can be read as an email id. The separator in email ids is the character “@”. So, our query is,

SELECT CONCAT_WS(“@”, “support”, “gmail.com”) AS Email_ID;Code language: SQL (Structured Query Language) (sql)

And our output is,

Concat Ws Example1

2. CONCAT_WS() With ROW Values

Consider the below ConferenceGuests table.

Concat Conference Guests Table
Conference Guests Table

Let us make an Address field which has the concatenated string containing the value of State and Country, separated by a comma and whitespace. Let us display the guest names too in a different field.

SELECT Name, CONCAT_WS(", ", State, Country) AS Address FROM ConferenceGuests;Code language: SQL (Structured Query Language) (sql)

And we get our output as,

Concat Ws With Row Values

Conclusion

CONCAT_WS() can be very useful to present your output in a more readable and understandable value. String functions like CONCAT_WS() are very important to understand and so I hope you’ll take a look at the link in the references below.

References