MySQL Concat String

The MySQL Concat is one of the String Functions, which helps combine two or more sentences and return the string as an output. If anyone of the argument passed to this Function is a Null value, it displays the result as NULL. The basic syntax of string Concat is as shown below:

SELECT CONCAT (String 1, String 2,.., String N)
FROM Source

This function returns the binary string that results from concatenating the arguments string1, string2…stringN. To demonstrate this MySQL Concat string function, We are going to use the below table data that we have shown.

MySQL Concat String Example

The method returns the string by concatenating two or more sentences or arguments. For example, the following query shows you the same.

SELECT CONCAT('Learn', ' MySQL Server', ' For Free') AS 'MySQLTutorial'

As you can see from the screenshot below, it combines Learn, MySQL Server, and For Free and returns the combined sentence as a result.

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

SELECT CONCAT('Learn', ' MySQL Server', NULL) AS 'MySQLTutorial'

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

Concatenation Example 2

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

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

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

MySQL Concat String with Nulls example

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

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 four rows.

Concat String Where Clause

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

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 this, you might use the Alias name Full Name in the where clause. But, the 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 the ending word smith.

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';

Command prompt example

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

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;