MySQL Concat String

The MySQL Concat is one of the String Function, which is used to combine two or more strings and returns string. In this article we will show you, How to write a MySQL concat string function using command prompt, and workbench with example.

If any one of the argument passed to MySQL CONCAT String Function is Null value, then it will display 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

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

MySQL Concat String Example 1

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

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

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

Let me try to pass 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, it returned the result as NULL

Concat String Example 2

In this example, we are going 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 first name and last name. If you want comma or something just replace empty space with comma

MySQL Concat String Handling Nulls example

In this example we will show you, How concat function will react if it face NULL values in a table rows. We are going 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 will show you, 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

This 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 will write a concat function in Command prompt. Here, we are using 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