MySQL CONCAT() Function with Examples


How to Use the MySQL CONCAT() Function

In this tutorial, you'll learn to use the MySQL CONCAT() function. CONCAT() is a function to add two or more expressions together.

MySQL CONCAT() Syntax

CONCAT(expression1, expression2,..., expressionN)

Remarks

  • The first expression is required
  • CONCAT returns NULL if any expression NULL

Examples

Combining Several Strings

The following illustrates an example of using  CONCAT() to combine several strings:

SELECT CONCAT("I ", "want ", "to visit ", "United State") AS Combined_String;

The statement returns:

I want to visit United State.

Combining Several Strings with NULL

The following statement returns NULL since the 3rd argument is a NULL value:

SELECT CONCAT("I ", "Like playing football", NULL);

More Examples

Below is the demo orders table:

order_idorder_numbercustomer_codecustomer_namecountryamount
1#ORD_00001#CID_20001Josh WarnerJapan1980.00
2#ORD_00002#CID_20002Lisa RamseyUnited State1350.50
3#ORD_00003#CID_20001Josh WarnerJapan1230.40
4#ORD_00004#CID_20001Josh WarnerJapan1200.00
5#ORD_00005#CID_20002Lisa RamseyUnited State3450.00
6#ORD_00006#CID_20003Charles HarmonUnited State3470.50
7#ORD_00007#CID_20004Francis LewisGermany1230.80
8#ORD_00008#CID_20005William BarnettChina1998.80

You can use the SUM() function to sum the values, then combine with other strings together.

Here is an example:

SELECT CONCAT("The total amount is $", SUM(o.amount), ".") AS Result
FROM orders o;

The returned value is as follows:

Result
The total amount is $15911.00.

Also, you can use SUM() and GROUP BY with the CONCAT() function.

Here is an example:

SELECT CONCAT("The total amount ", "ordered by ", o.customer_name, " is $", SUM(o.amount), ".") AS Result
FROM orders o GROUP BY o.customer_name;

The returned result set is as follows:

Result
The total amount ordered by Charles Harmon is $3470.50.
The total amount ordered by Francis Lewis is $1230.80.
The total amount ordered by Josh Warner is $4410.40.
The total amount ordered by Lisa Ramsey is $4800.50.
The total amount ordered by William Barnett is $1998.80.

In this tutorial, you’ve learned how to use the MySQL CONCAT() function. You can use CONCAT() to combine two or more strings.

Leave a Reply

Your email address will not be published. Required fields are marked *