MySQL - CONCAT() Function

The MySQL CONCAT() function is used to concatenate two or more strings into a single string.

It takes two or more string values as input and returns a single string by concatenating all the input strings in the order in which they were provided.

  • If we provide nonbinary strings as input, it returns a nonbinary string as output.

  • If we provide binary strings as input, it returns a binary string as output.

  • If we provide numeric values as input, it is converted into equivalent nonbinary string form.

When working with MySQL tables, this function can be useful in various scenarios such as combining the first name and last name of a person into a full name, concatenating strings with separators such as (" "), (-), (,), etc.

Syntax

Following is the syntax of MySQL CONCAT() function −

CONCAT(str1,str2,...)

Parameters

This function takes multiple string values as parameter.

Return Value

This function returns the concatenation of the given string values, combining them into a single string.

Example

In this example, we are using the CONCAT() function to concatenate two strings, 'tutorials' and 'point' −

SELECT CONCAT('tutorials', 'point');

Following is the output of the above code −

CONCAT('tutorials', 'point')
tutorialspoint

Example

Here is another example where we are passing only one argument to the CONCAT() function −

SELECT CONCAT('test');

Output of the above code is as shown below −

CONCAT('test')
test

Example

You can concatenate by passing more than two arguments as well −

SELECT CONCAT('Shyam', 'Singa', 'Roy');

The output obtained is as shown below −

CONCAT('Shyam', 'Singa', 'Roy')
ShyamSingaRoy

Example

If you pass binary strings as parameters to this function, it returns a binary string −

SELECT CONCAT('110110', '1100110');

The result produced is as follows −

CONCAT('110110', '1100110')
1101101100110

Example

Similarly, if you pass nonbinary values as arguments, it returns a nonbinary string −

SELECT CONCAT('sample', 'test');

We get the output as follows −

CONCAT('sample', 'test')
sampletest

Example

You can also combine both binary and nonbinary values and pass it as an argument to this function −

SELECT CONCAT('sample', 1010011);

The output obtained is as shown below −

CONCAT('sample', 1010011)
sample1010011

Example

When you pass a numeric value as an argument, the CONCAT() function converts it into its equivalent nonbinary string form −

SELECT CONCAT(1125, 225, 669);

The result produced is as follows −

CONCAT(1125, 225, 669)
1125225669

Example

If you pass an empty string as an argument to the CONCAT() function, it returns '0' −

SELECT CONCAT('');

We get the output as follows −

CONCAT('')
0

Example

If one of the arguments passed to the CONCAT() function is NULL, the result will also be NULL −

SELECT CONCAT(1125, 225, NULL);

Following is the output of the above code −

CONCAT(1125, 225, NULL)
NULL

Example

You can also pass column names of a table as arguments to this function and concatenate/join the values in the given columns.

Let us create a table named "PLAYERS" and insert records into it using CREATE and INSERT statements as shown below −

CREATE TABLE PLAYERS(
   ID INT,
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Date_Of_Birth date,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255),
   PRIMARY KEY (ID)
);

Now, let us insert records into it using the INSERT statement −

INSERT INTO PLAYERS VALUES
(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India'),
(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica'),
(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka'),
(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India'),
(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India'),
(6, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');

The PLAYERS table obtained is as follows −

ID First_Name Last_Name Date_Of_Birth Place_Of_Birth Country
1 Shikhar Dhawan 1981-12-05 Delhi India
2 Jonathan Trott 1981-04-22 CapeTown SouthAfrica
3 Kumara Sangakkara 1977-10-27 Matale Srilanka
4 Virat Kohli 1988-11-05 Delhi India
5 Rohit Sharma 1987-04-30 Nagpur India
6 James Anderson 1982-06-30 Burnley England

Following query concatenates/joins the First_Name and Last_Name values of the PLAYERS table and displays the result as FullName −

SELECT CONCAT (First_Name, Last_Name) as FullName, Country 
FROM PLAYERS;

Output

After executing the above code, we get the following output −

FullName Country
ShikharDhawan India
JonathanTrott SouthAfrica
KumaraSangakkara Srilanka
ViratKohli India
RohitSharma India
JamesAnderson England

Example

Here, we are concatenating more than 2 columns i.e. "First_name", "Last_name", and "Country" using the following query −

SELECT CONCAT (First_Name, " ", Last_Name, " ", Country) as FullName_and_Country
FROM PLAYERS;

Output

The output displayed is as shown below −

FullName_and_Country
Shikhar Dhawan India
Jonathan Trott SouthAfrica
Kumara Sangakkara Srilanka
Virat Kohli India
Rohit Sharma India
James Anderson England
mysql-concat-function.htm
Advertisements