MySQL CONCAT() function
In this context, we will learn how we can use the MySQL CONCAT() function with proper syntax and good examples.
Introduction of MySQL CONCAT() function
By using the MySQL CONCAT function, we will learn various ways to concatenate two or more strings together. To concatenate two or more quoted string values, we will place the string next to each other.
For example, if we use PostgreSQL or Oracle, we have to use the string concatenation operator ||. In the Microsoft SQL server, you have to use the addition arithmetic operator (+) to concatenate string values.
Besides using spaces for string concatenation, MySQL provides two other functions that concatenate string values: CONCAT and CONCAT_WS.
Syntax of the MySQL CONCAT() function
The syntax of the MySQL CONCAT() function is given as follows:
SELECT 'MySQL ' 'String ' 'Concatenation';
CONCAT(string1,string2, ... );
Parameters or arguments used in MySQL CONCAT() function:
The MySQL CONCAT function takes one or more string arguments and concatenates them into a single string. The CONCAT function requires a minimum of one parameter; otherwise, it raises an error.
The CONCAT function converts all arguments to the string type before concatenating. If any argument is NULL, the CONCAT function returns a NULL value.
Application used for CONCAT() function:
The CONCAT() function can be used in the given below MySQL versions.:
- MySQL 5.7
- MySQL 5.6
- MySQL 5.5
- MySQL 5.1
- MySQL 5.0
- MySQL 4.1
- MySQL 4.0
- MySQL 3.23
Examples of MySQL CONCAT() function:
Now we will look into some MySQL CONCAT() function examples and will explore how we can use the CONCAT function in MySQL.
The following statement concatenates two quoted strings: MySQL and CONCAT.
SELECT CONCAT('MySQL', 'CONCAT');
Output:
| CONCAT(‘MYSQL’,’CONCAT’) |
| MYSQL CONCAT |
If you pass a NULL value as the argument in the CONCAT function, then the function returns a NULL value:
SELECT CONCAT('MySQL', NULL, 'CONCAT');
Output:
| CONCAT(‘MYSQL’,NULL,’CONCAT’) |
| NULL |
Now, we are going to use the Concat Function with the MySQL table:
Let’s take the following consumer table as an example:
| Consumers |
| consumerNumber consumername consumerfirstname consumerlastname phonenumber address1 address2 city state pincode country salesreportworkernumber |
To get the full names of consumer, you use the CONCAT function to concatenate first name, space, and last name as the following statement:
SELECT
concat(consumerfirstname,' ',consumerlastname) AS Fullname
FROM consumers;
Output:
| Fullname |
| Dinesh sahoo Saswat sahoo Deepak sahoo Animesh behera Lipika paramanik Aniket Mishra Aman sahoo Suman mishra |
Some Advanced Examples of Concat Function
Example-1 :
Now we will Concatenate 3 strings using CONCAT Function in MySQL.
SELECT CONCAT('java', 't', 'points') AS ConcatenatedString ;
Output:
| ConcatenatedString |
| javatpoints |
Example-2:
Now we will Concatenate numeric strings using CONCAT Function in MySQL.
SELECT CONCAT(21, 01, 6.60) AS ConcatenatedNumber ;
Output :
| ConcatenatedNumber |
| 21016.60 |
Example-3:
Now we will Concatenate a string which includes a NULL String using CONCAT Function in MySQL.
SELECT CONCAT('java', 't', 'points', NULL) AS ConcatenatedString ;
Output:
| ConcatenatedString |
| NULL |
Example-4:
When we require concatenating string between column data, we can use the CONCAT function's help. In this example, we will concatenate strings between a table's column. For demonstration, we have created a table named pupil.
CREATE TABLE Pupil(
PupilId 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(PupilId )
);
Now insert some data into the Pupil table:
INSERT INTO
Pupil(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', 'Sharma', 'X', 'Kolkata', 'WestBengal', 700004 ) ;
So, the Pupil table is:
Select * From Pupil;
| PupilId | 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 as FullName and City, State and PinNo as Address using CONCAT Function.
Select
PupilId, FirstName, LastName,
CONCAT(FirstName, ' ', LastName) AS FullName,
CONCAT(City, ' ', State, ' ', PinNO) AS Address
FROM Pupil;
Output:
| PupilId | 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 |
Application of MySQL CONCAT() function:
This function is used to concatenate two or more strings together by using the MySQL CONCAT functions.
Summary:
In the above context, we have learned how we can use the CONCAT() function in MySQL used to concatenate two or more strings together.