How to concatenate strings using the MySQL CONCAT() function and examples
This tutorial explains how to concatenate strings using the MySQL CONCAT() function.
You will learn how to use the MySQL CONCAT() function, which is a function that adds two or more expressions together.
Learn more: MySQL GROUP_CONCAT() function
MySQL CONCAT() Syntax
CONCAT(expression1, expression2,..., expressionN)
Remarks
- The first expression is required.
- CONCAT() returns NULL if any expression is NULL.
Examples
1. Combine several strings
The following is an example of using the MySQL CONCAT() to concatenate multiple strings:
SELECT CONCAT("I ", "want ", "to visit ", "United State") AS Combined_String;
The statement returns:
I want to visit United State.
2. Combine several strings with NULL
The following statement returns null because the third argument is a NULL value:
SELECT CONCAT("I ", "Like playing football", NULL);
More Examples
Below is the order list for the demo:
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 MySQL SUM() function to sum the values and combine them with other strings.
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. |
You can also 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. |
Summary
In this tutorial, you have learned how to concatenate strings using the MySQL CONCAT() function. You can use MySQL CONCAT() to combine multiple strings.