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_id | order_number | customer_code | customer_name | country | amount |
---|---|---|---|---|---|
1 | #ORD_00001 | #CID_20001 | Josh Warner | Japan | 1980.00 |
2 | #ORD_00002 | #CID_20002 | Lisa Ramsey | United State | 1350.50 |
3 | #ORD_00003 | #CID_20001 | Josh Warner | Japan | 1230.40 |
4 | #ORD_00004 | #CID_20001 | Josh Warner | Japan | 1200.00 |
5 | #ORD_00005 | #CID_20002 | Lisa Ramsey | United State | 3450.00 |
6 | #ORD_00006 | #CID_20003 | Charles Harmon | United State | 3470.50 |
7 | #ORD_00007 | #CID_20004 | Francis Lewis | Germany | 1230.80 |
8 | #ORD_00008 | #CID_20005 | William Barnett | China | 1998.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.