MySQL CONCAT() Function [With Easy Examples]

By /
Mysql Concat

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.

Note that, 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 SELECT statement.

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.

Concat Students Table
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,

Concat Basic Example

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,

Concat Row Values

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,

Concat Adding Null To Table

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,

Concat Null Value

It is important to remember that the result of CONCAT() is NULL if any argument value happens to be NULL.

Conclusion

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.

References