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.