In a previous tutorial, we studied the MySQL
CONCAT() function. Building on that, this tutorial will focus on 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
Syntax of MySQL CONCAT_WS()
CONCAT_WS(separator, expression1, expression2, expression3,...)
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;
Our output is,
“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.
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;
And our output is,
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;
And our output is,
2. CONCAT_WS() With ROW Values
Consider the below ConferenceGuests 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;
And we get our output as,
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.
- MySQL Official Documentation on