Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Python Programs
    • Java Programs
    • SQL FAQ’s

MySQL Concat String

by suresh

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

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.

MySQL Concat String Function 0

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

MySQL Concat String Function 1

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

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

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

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

MySQL Concat String Function 5

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

MySQL Concat String Function 6

Placed Under: MySQL

  • How to Download MySQL
  • Install MySQL on Windows
  • MySQL Create Database
  • MySQL Delete Database
  • MySQL Create Table
  • MySQL Drop Table
  • MySQL SELECT Statement
  • MySQL ALIAS Column
  • MySQL Distinct
  • MySQL Insert Statement
  • MySQL Delete
  • MySQL Truncate Table
  • MySQL WHERE Clause
  • MySQL Order By
  • MySQL Group By
  • MySQL Having Clause
  • MySQL LIMIT
  • MySQL Arithmetic Operators
  • MySQL COALESCE Function
  • MySQL AND Operator
  • MySQL NOT Operator
  • MySQL OR Operator
  • MySQL XOR Operator
  • MySQL BETWEEN Operator
  • MySQL Not Between Operator
  • MySQL GREATEST Function
  • MYSQL LEAST Function
  • MySQL LIKE Operator
  • MySQL NOT LIKE Operator
  • MySQL IFNULL Operator
  • MySQL NULLIF Operator
  • MySQL INTERVAL Operator
  • MySQL IS Operator
  • MySQL IN Operator
  • MySQL NOT IN Operator
  • MySQL IS NOT NULL
  • MySQL IS NULL
  • MySQL Inner Join
  • MySQL Cross Join
  • MySQL Right Join
  • MySQL Left Join
  • MySQL Aggregate Functions
  • MySQL Date Functions
  • MySQL Date Function
  • MySQL String Functions
  • MySQL Numeric Functions
  • C Tutorial
  • C# Tutorial
  • Java Tutorial
  • JavaScript Tutorial
  • Python Tutorial
  • MySQL Tutorial
  • SQL Server Tutorial
  • R Tutorial
  • Power BI Tutorial
  • Tableau Tutorial
  • SSIS Tutorial
  • SSRS Tutorial
  • Informatica Tutorial
  • Talend Tutorial
  • C Programs
  • C++ Programs
  • Java Programs
  • Python Programs
  • MDX Tutorial
  • SSAS Tutorial
  • QlikView Tutorial

Copyright © 2021 | Tutorial Gateway· All Rights Reserved by Suresh

Home | About Us | Contact Us | Privacy Policy