CONCAT() function in MySQL

CONCAT() function in MySQL is used to concatenating the given arguments. It may have one or more arguments. If all arguments are nonbinary strings, the result is a nonbinary string. If the arguments include any binary strings, the result is a binary string. If a numeric argument is given then it is converted to its equivalent nonbinary string form. Syntax :

CONCAT(str1, str2, ...)

Parameter : This method accepts N argument.  

• str1, str2.str3…. : The input string which we want to concatenate.

Returns : It returns a new string after concatenating all input string. If any of the input string is NULL then it returns NULL. Example-1 : Concatenating 3 string using CONCAT Function.

SELECT CONCAT('geeks', 'for', 'geeks') AS ConcatenatedString ;

Output :

ConcatenatedString
geeksforgeeks

Example-2 : Concatenating numeric string using CONCAT Function.

SELECT CONCAT(19, 10, 5.60) AS ConcatenatedNumber ;

Output :

ConcatenatedNumber
19105.60

Example-3 : Concatenating string which includes a NULL String using CONCAT Function.

SELECT CONCAT('geeks', 'for', 'geeks', NULL) AS ConcatenatedString ;

Output :

ConcatenatedString
NULL

Example-4 : In this example we are going to concatenate string between column of a table. To demonstrate create a table named Student.

CREATE TABLE Student(

StudentId INT AUTO_INCREMENT,  
FirstName VARCHAR(100) NOT NULL,
LastName VARCHAR(100) NOT NULL,
Class VARCHAR(20) NOT NULL,
City VARCHAR(20) NOT NULL,
State VARCHAR(20) NOT NULL,
PinNo INT  NOT NULL,
PRIMARY KEY(StudentId )

);

Now inserting some data to the Student table :

INSERT INTO  
Student(FirstName, LastName, Class, City, State, PinNo )
VALUES
('Sayantan', 'Maity', 'X', 'Kolkata', 'WestBengal', 700001 ),
('Nitin', 'Shah', 'XI', 'Jalpaiguri', 'WestBengal', 735102 ),
('Aniket', 'Sharma', 'XI', 'Midnapore', 'WestBengal', 721211 ),
('Abdur', 'Ali', 'X', 'Malda', 'WestBengal', 732101 ),
('Sanjoy', 'Sharama', 'X', 'Kolkata', 'WestBengal', 700004 ) ;

So, the Student table is :

Select * From Student ;

StudentId	FirstName	LastName	Class	City	State	PinNo
1	Sayantan	Maity	X	Kolkata	WestBengal	700001
2	Nitin	Shah	XI	Jalpaiguri	WestBengal	735102
3	Aniket	Sharma	XI	Midnapore	WestBengal	721211
4	Abdur	Ali	X	Malda	WestBengal	732101
5	Sanjoy	Sharama	X	Kolkata	WestBengal	700004

Now, we will concatenate FirstName and LastName to get FullName and City, State and PinNo to get Address using CONCAT Function.

Select 
    StudentId, FirstName, LastName, 
    CONCAT(FirstName, ' ', LastName) AS FullName,
    CONCAT(City, ' ', State, ' ', PinNO) AS Address

    FROM Student;    

Output :

StudentId	FirstName	LastName	FullName	Address
1	Sayantan	Maity	Sayantan Maity	Kolkata WestBengal 700001
2	Nitin	Shah	Nitin Shah	Jalpaiguri WestBengal 735102
3	Aniket	Sharma	Aniket Sharma	Midnapore WestBengal 721211
4	Abdur	Ali	Abdur Ali	Malda WestBengal 732101
5	Sanjoy	Sharama	Sanjoy Sharama	Kolkata WestBengal 700004