Skip to content
Related Articles

Related Articles

CONCAT_WS() Function in MySQL
  • Last Updated : 03 Dec, 2020
GeeksforGeeks - Summer Carnival Banner

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_IdFirstNameLastNameResidenceSalary
1AnimeshGargDelhi70000
2NeshuSharmaNepal73000
3AryanSharmaWestBengal72000
4AbdulAliDelhi73000
5SeemSharmaBihar70000

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

Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up
Recommended Articles
Page :