MySQL CONCAT_WS() Function

The MySQL CONCAT_WS() function, which stands for "Concatenate With Separator", is used to concatenate (combine) multiple strings into a single string, separating them with a specified separator.

The first argument of this function is considered as the separator. It can be a string or a numerical value. If the separator value is NULL, this function returns NULL.

If there are any empty stings in the string values, they will be included in the result. But if you pass NULL as a string value (after the separator), it is neglected.

The string functions in SQL are used to manipulate the string values. There are many string functions specified for SQL and CONCAT_WS() is one of them.

Syntax

Following is the syntax of MySQL CONCAT_WS() function −

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

Parameters

This function takes a separator and multiple string values as parameter.

Return Value

This function returns the concatenation of the given strings with the specified separator.

Example

In this example, we are using the CONCAT_WS() function to concatenate the strings 'tutorials' and 'point' with an underscore as the separator −

SELECT CONCAT_WS('_','tutorials', 'point');

Following is the output of the above code −

CONCAT_WS('_','tutorials', 'point')
tutorials_point

Example

Here is another example where we are passing a space character as the separator, joining the strings 'Hello', 'how', 'are', and 'you' −

SELECT CONCAT_WS(' ','Hello', 'how', 'are', 'you');

Output of the above code is as shown below −

CONCAT_WS(' ','Hello', 'how', 'are', 'you')
Hello how are you

Example

You can also provide numerical values as separators, as shown below −

SELECT CONCAT_WS('999', 'Hello', 'how', 'are', 'you');

The output obtained is as shown below −

CONCAT_WS('999', 'Hello', 'how', 'are', 'you')
Hello999how999are999you

Example

If you pass a NULL value as a separator, it returns NULL −

SELECT CONCAT_WS(NULL, 'test', 'sample');

The result produced is as follows −

CONCAT_WS(NULL, 'test', 'sample')
NULL

Example

In the following example, we are passing an empty string after the separator and, as you observe it is included in the result −

SELECT CONCAT_WS('_____','str1','str2', 'str3', '     ', 'str4');

We get the output as follows −

CONCAT_WS('_____','str1','str2', 'str3', ' ', 'str4')
str1_____str2_____str3_____ _____str4

Example

If you provide NULL as a string value after the separator, it is ignored and not included in the result −

SELECT CONCAT_WS('_____','str1','str2', 'str3', NULL, 'str4');

The output obtained is as shown below −

CONCAT_WS('_____','str1','str2', 'str3', NULL, 'str4')
str1_____str2_____str3_____str4

Example

You can also pass column names of a table as arguments to this function (along with the separator) and concatenate/join the values in the given columns.

Let us create a table named "PLAYERS" and insert records into it using CREATE and INSERT statements as shown below −

CREATE TABLE PLAYERS(
   ID INT,
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Date_Of_Birth date,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255),
   PRIMARY KEY (ID)
);

Now, let us insert records into it using the INSERT statement −

INSERT INTO PLAYERS VALUES
(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India'),
(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica'),
(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka'),
(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India'),
(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India'),
(6, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');

The PLAYERS table obtained is as follows −

ID First_Name Last_Name Date_Of_Birth Place_Of_Birth Country
1 Shikhar Dhawan 1981-12-05 Delhi India
2 Jonathan Trott 1981-04-22 CapeTown SouthAfrica
3 Kumara Sangakkara 1977-10-27 Matale Srilanka
4 Virat Kohli 1988-11-05 Delhi India
5 Rohit Sharma 1987-04-30 Nagpur India
6 James Anderson 1982-06-30 Burnley England

Following query concatenates/joins the First_Name and Last_Name values of the PLAYERS table using an empty string as the separator and displays the result as FullName −

SELECT CONCAT (' ', First_Name, Last_Name) as FullName, Country 
FROM PLAYERS;

Output

After executing the above code, we get the following output −

FullName Country
Shikhar Dhawan India
Jonathan Trott SouthAfrica
Kumara Sangakkara Srilanka
Virat Kohli India
Rohit Sharma India
James Anderson England

Example

Here, we are concatenating more than 2 columns i.e. "First_name", "Last_name", and "Country" with an empty space (" ") as a separator using the following query −

SELECT CONCAT (' ', First_Name, Last_Name, Country) as FullName_and_Country
FROM PLAYERS;

Output

The output displayed is as shown below −

FullName_and_Country
Shikhar Dhawan India
Jonathan Trott SouthAfrica
Kumara Sangakkara Srilanka
Virat Kohli India
Rohit Sharma India
James Anderson England
mysql-concat-ws-function.htm
Advertisements