MySQL Concat String

The MySQL Concat is one of the String Functions, which is useful to combine two or more strings and returns string. If anyone of the argument passed to MySQL CONCAT String Function is Null value, then it displays the result as NULL.

The basic syntax of string Concat in MySQL is as shown below:

-- MySQL String Concat Syntax 
SELECT CONCAT (String 1, String 2,.., String N)
FROM Source

To demonstrate this MySQL concat string function, We are going to use the employ table data that we have shown below

MySQL Concat String Example 1

The Concat String Function returns the string by combining two or more strings. The following concat query show you the same.

-- MySQL String Concat Example
SELECT CONCAT('Learn', ' MySQL Server', ' For Free') AS 'MySQL Tutorial'

As you can see from the screenshot below, it is combining Learn, MySQL Server, For Free, and returning the combined string as a result.

Let me try to pass a NULL value as the third argument.

-- MySQL String Concat Example
SELECT CONCAT('Learn', ' MySQL Server', NULL) AS 'MySQL Tutorial'

As you can see from the below screenshot, this String Function returned the result as NULL

Concat String Example 2

In this example, we are going to combine the First_Name and Last_Name columns present in the Employ table to get Full Name.

-- MySQL String Concat Example
USE company;
SELECT  First_Name, Last_Name,
	CONCAT(First_Name,' ', Last_Name) AS 'Full Name',
	Education, Profession, Yearly_Income, Sales
FROM customerdetails;

OUTPUT

TIP: We used this ‘ ‘ to get empty space between the first name and last name. If you want comma or something replace space with comma

MySQL Concat String Handling Nulls example

In this example, we show you, How concat function reacts if it faces NULL values in a table row. We are going to combine the Education and Sales along with sample text.

-- MySQL String Concat Example
USE company;
SELECT  First_Name, Last_Name,
	Education, Profession,        
        Yearly_Income, Sales,
        CONCAT(Education, 'Sales Amount is: ', Sales) AS 'Sales Info'
FROM customerdetails;

As you can see from the below screenshot, it returned the result as NULL for 4 rows.

MySQL Concat String Where Clause

In this example, we show how to use Concat function in where clause. First, we used concat on First_Name and Last_Name columns to get Full Name. Next, we used the same function in where clause as well to check whether the full name is equal to Tutorial Gateway.

-- MySQL String Concat Example
USE company;
SELECT  First_Name, Last_Name,
	CONCAT(First_Name,' ', Last_Name) AS 'Full Name',
	Education, Profession, Yearly_Income, Sales
FROM customerdetails
WHERE CONCAT(First_Name,' ', Last_Name) = 'Tutorial Gateway';

Instead of using the Concat function, you might be tempted to use the Alias name Full Name in where clause. But, WHERE Clause does not understand the ALIAS Column name declared in the SELECT Statement.

MySQL Concat String using Wildcards

It is the same example that we used in our previous example. But, this time we used the Wildcard to search for ending word Smith

-- MySQL String Concat Example
USE company;
SELECT  First_Name, Last_Name,
	CONCAT(First_Name,' ', Last_Name) AS 'Full Name',
	Education, Profession, Yearly_Income, Sales
FROM customerdetails
WHERE CONCAT(First_Name,' ', Last_Name) LIKE '%Smith';

OUTPUT

Concat String Command prompt example

In this example, we write a concat function in Command prompt. Here, we are using a concat string function to combine Profession, Sales, and Yearly Income along with some static text.

-- MySQL String Concat Example
USE company;
SELECT  CONCAT(First_Name, ' ', Last_Name) AS 'Full Name',
	    Education,      
        CONCAT(Profession, ' Person of Sales : ', Sales, 
               ' is earning : ', Yearly_Income, ' Per Year') AS 'Sales Info'
FROM customerdetails
ORDER BY Education;

OUTPUT