CONCAT_WS() Function in MySQL

CONCAT_WS() :
This function in MySQL helps in joining two or more strings along with a separator. The separator must be specified by the user and it can also be a string. If the separator is NULL, then the result will also be NULL.

Syntax :

CONCAT_WS(separator, string1, string2, ...)

Parameters :

• separator –
  A separator which will be added between the strings while concatenation string1, string2, etc.
• [string1, string2 …] –
  The input strings which needed to be concatenated.

Return :
It will return a new string, after concatenating all given strings, along with a specified separator. And if all input strings are NULL, then the result will be NULL. If the separator is NULL, it will return NULL.

Example 1 :
Concatenating 2 strings using CONCAT_WS Function as follows.

SELECT CONCAT_WS(": ", "Geek ", "Vansh ") AS ConcatWsStr;

Output :

ConcatWsStr
Geek : Vansh

Example-2 :
Concatenating 3 strings using CONCAT_WS Function as follows.

SELECT CONCAT_WS("@ ", "Geek ", "Vansh ", 13 ) AS ConcatWsStr;

Output :

ConcatWsStr
Geek @ Vansh @ 13

Example-3 :
Concatenating a NULL string using NULL separator as follows.

SELECT CONCAT_WS(NULL, NULL, "Vansh ", 13 ) AS ConcatWsStr;

Output :

ConcatWsStr
NULL

Example-4 :
Concatenating the columns of a table using CONCAT_WS Function as follows.

Creating an Employee table :

CREATE TABLE Emp(
Employee_Id INT AUTO_INCREMENT,  
FirstName VARCHAR(100) NOT NULL,
LastName VARCHAR(100) NOT NULL,
Residence VARCHAR(50) NOT NULL,
Salary INT  NOT NULL,
PRIMARY KEY(Employee_Id )
);

Inserting data into the Table :

INSERT INTO Emp(FirstName, LastName, Residence, Salary )
VALUES
('Animesh', 'Garg', 'Delhi', 70000 ),
('Neshu', 'Sharma', 'Nepal', 73000 ),
('Aryan', 'Sharma', 'WestBengal', 72000 ),
('Abdul', 'Ali', 'Delhi', 73000 ),
('Seema', 'Sharma', 'Bihar', 70000 ) ;

To verify used the following command as follows.

Select * From Emp;

Output :

Employee_Id	FirstName	LastName	Residence	Salary
1	Animesh	Garg	Delhi	70000
2	Neshu	Sharma	Nepal	73000
3	Aryan	Sharma	WestBengal	72000
4	Abdul	Ali	Delhi	73000
5	Seem	Sharma	Bihar	70000

Now, concatenate FirstName and LastName of given Emp table using ‘_’ as a separator to form a new column as FullName.

SELECT CONCAT_WS('_', FirstName,  LastName) AS FullName
From Emp;  

Output :

FullName
Animesh_Garg
Neshu_Sharma
Aryan_Sharma
Abdul_Ali
Seema_Sharma