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 sting 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
My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.