MySQL CONCAT
Overview
The MySQL CONCAT() function is a built-in function that concatenates all its input strings together to return a single string value. The CONCAT() function can also be used to concatenate strings that contain special characters such as apostrophes and quotes.
Syntax of MySQL CONCAT() Function
The basic syntax for the MySQL CONCAT() function is as follows:
Here, string1, string2, string3, etc are all the input parameters for the CONCAT() function. Ultimately, it will concatenate all the input strings together in the same order as they have been specified in the CONCAT() function. The output will be a single string.
Transform Your Career
Choose from our industry-leading programs designed for career success
Modern Software and AI Engineering Program
Master full-stack development with AI integration
+1000 moreModern Data Science and ML with specialisation in AI
Advanced data science techniques with AI specialization
+1000 moreAdvanced AIML with Specialisation in Agentic AI
Deep dive into AIML with focus on Agentic systems
+1000 moreDevOps, Cloud & AI Platform Engineering
Build and manage AI-powered cloud infrastructure
+1000 moreAI Engineering Advanced Certification by IIT-Roorkee
Premier AI engineering certification from IIT-Roorkee
Parameters of MySQL CONCAT() Function
The CONCAT() function takes one or more string expressions as its parameters which are separated by commas. It can take anywhere between 1 to N parameters where N can be the maximum number of arguments allowed.
Each string parameter can either be a string literal enclosed in quotes or it can also be a column name that contains string values. We'll see how the columns can also be concatenated in the example section. But here's how you can write a CONCAT() statement to concatenate two columns into one.
Return Value of MySQL CONCAT() Function
MySQL CONCAT() function always returns a new single string that is the result of appending all the input strings in the same order as they had been specified.
Output:
| GREET |
|---|
| Hey there! |
If you wish to get a non-NULL string as the output value, make sure you only have non-NULL values in the input. There can be a blank input string with spaces though.
Here, the CONCAT() function has concatenated all the input strings with an alias for the concatenated result.
Exceptions of MySQL CONCAT() Function
The only exception with the MySQL CONCAT() function is that there should not be any of the NULL input strings.
For example, considering the query given below, the output of the statement will be NULL.
Output:
| GREET |
|---|
| NULL |
Although the CONCAT() function has taken all four arguments, the resultant concatenated string will be NULL because there was a NULL input string in the statement.
How does the MySQL CONCAT() Function Work?
The MYSQL CONCAT() function works by considering one or more input strings and concatenating them all into a single output string.
In addition to simply appending the input strings, it can also be used with some other functions and expressions to concatenate more complex strings as well.
Examples
Scaler Placement Report and Statistics
Scaler learners achieved 2.5x salary growth with average post-Scaler CTC reaching ₹23L.
Google
Amazon
Microsoft
Flipkart
Adobe1200+ moreConcatenating Multiple Input Strings Using CONCAT() Function
In this example, we'll have several input strings to get a resultant sentence.
Output:
| GREET |
|---|
| Hey there! How you doin'? |
Concatenating All the Arguments Along With a NULL Value
Output:
| GREET |
|---|
| NULL |
But, there can be a simple workaround. Imagine a case, where you've written a query to concatenate a string using the CONCAT() function, but there's one variable that may or may not be NULL. So, you can use the IF() function in such case in such scenarios.
It evaluates the first argument and if it's true, then it returns exp2, or else, it will return exp3 if exp1 is evaluated to be false.
Output:
| GREET |
|---|
| Hello John! |
Here, we had put the condition to add an extra space only when there's a valid name with the help of another CONCAT() function inside the primary function.
If the name would have been set to NULL, then the space after Hello will eventually be removed.
Output:
| GREET |
|---|
| Hello! |
Concatenating Numeric String Using CONCAT() Function
Numeric strings can also be concatenated with the CONCAT() function.
Output:
| CONCAT('123', '456', '7.89') |
|---|
| 1234567.89 |
Turn Learning into Career Growth
Concatenating a String With Another Function
We have seen previously how a string can be concatenated with another function. Here's another example:
Output:
| TODAY |
|---|
| The current date is 2023-04-22 |
Concatenating a String With Other Data Types
A string can be concatenated with other data types using MySQL's CONCAT() function.
Output:
| Price |
|---|
| The price is $19.99 |
Concatenating Multiple Columns and Strings
Let's create a table for users with first_name, last_name, and their orders that are still pending.
Here's the query to create the table and insert some data into it.
Output:
| id | first_name | last_name | orders |
|---|---|---|---|
| 1 | John | Doe | 10 |
| 2 | Jane | Middleton | 5 |
| 3 | Bob | Smith | NULL |
| 4 | Nick | Walter | 7 |
To display the result as a single string, we'll use the following query that includes a CONCAT() function as well as an IFNULL() function to return an alternate value if the value for orders is NULL.
Output:
| Total Orders |
|---|
| Hi John Doe! You have 10 orders pending. |
| Hi Jane Middleton! You have 5 orders pending. |
| Hi Bob Smith! You have no orders pending. |
| Hi Nick Walter! You have 7 orders pending. |
Concatenating a String With a Subquery
Conclusion
- The MYSQL CONCAT() function works by considering one or more input strings and concatenating them all into a single output string.
- The CONCAT() function takes one or more string expressions as its parameters which are separated by commas. It can take anywhere between 1 to N parameters.
- The only exception with the MySQL CONCAT() function is that there should not be any of the NULL input strings.
- It can also be used with some other functions and expressions to concatenate more complex strings