In this tutorial, we will learn about the MySQL
CONCAT() function. Suppose you have an Employee table that has separate columns for the first name and the last name of the employee.
What if you had to display the full name (first name + last name) of the employee in MySQL? Such a query would require you to combine the two values in both the rows and display them.
For such purposes, MySQL provides us with the
CONCAT() function. The
CONCAT() function is one of the many string functions of MySQL. It combines two or more row values.
For instance, if you have the following four rows in your table – Address_Line_1, City, State, Postal_Code; then
CONCAT() enables you to combine the values of these 4 rows for a particular record and you get it as one value.
CONCAT() doesn’t alter the structure of the table by combining rows. It only combines the row values for the given records while displaying them as an output. So,
CONCAT() is usually used with the
Syntax for MySQL CONCAT()
CONCAT(expression1, expression2, expression3,...)Code language: SQL (Structured Query Language) (sql)
Where expression includes row names or any value that you want to concat with another value.
Examples of MySQL CONCAT() function
Consider the below Students table.
1. Basic Example of MySQL CONCAT()
Before we move on to combining row values of a table using
CONCAT(), let’s take a look at a basic example. Consider the below query,
SELECT CONCAT('Hello!', 'I am ', '14', ' years of age.') AS ConcatenatedString;Code language: SQL (Structured Query Language) (sql)
We have 4 string arguments passed in the
CONCAT() function. MySQL
CONCAT() will append them in that order and then display them under the alias name
ConcatenatedString. We get the output as,
2. Combining Row Values using CONCAT()
Now let us work with the Students table. How about displaying the first name and last name of every student as one value under the alias of Full_Name?
SELECT CONCAT(FirstName, ' ', LastName) AS Full_Name FROM Students;Code language: SQL (Structured Query Language) (sql)
And we get our output as,
3. MySQL CONCAT() With NULL Values
Now let us see how the
CONCAT() function deals with NULL values. Before we go ahead, let us insert a row with a few NULL values in the Students table and then display the updated table.
INSERT INTO Students(ID, FirstName, DaysPresent) VALUES (7, 'Meera', 84); SELECT * FROM Students;Code language: SQL (Structured Query Language) (sql)
Our output is,
Now, what will happen if we combine the values of the FirstName and LastName columns for the student with ID=7? Let us use the following query with the
WHERE clause and find out!
SELECT CONCAT(FirstName, ' ', LastName) AS Full_Name FROM Students WHERE ID=7;Code language: SQL (Structured Query Language) (sql)
And we get the output as,
It is important to remember that the result of
CONCAT() is NULL if any argument value happens to be NULL.
CONCAT() can be very useful to present your output in a more readable and understandable value. String functions like
CONCAT() are very important to understand and so I hope you’ll take a look at the link in the references below.
- MySQL Official Documentation on