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 :

Example-2 : Concatenating numeric string using CONCAT Function.

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

Output :

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

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

Output :

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 ;

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 :

jana_sayantan

Follow

Improve

Article Tags :

• SQL
• DBMS-SQL
• mysql