MySQL Concat Function 6

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 this Function is Null value, then it displays the result as NULL. The basic syntax of string Concat in MySQL is as shown below:

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

The string 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 employ table data that we have shown below

MySQL Concat String Function Example

MySQL Concat String Example 1

The MySQL Concat String Function returns the string by concatenating two or more string arguments. The following concat query show you the same.

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

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

MySQL Concat String Function 0

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

MySQL Concat String Function 1

MySQL 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.

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

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 with 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.

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 Function 3

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.

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 Function 4

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

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';
MySQL Concat String Function 5

Concat String Command prompt example

In this example, we write a MySQL string concat function in Command prompt. Here, we are using this function to combine Profession, Sales, and Yearly Income along 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;
MySQL Concat String Function 6