MySQL CONCAT

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.

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:

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:

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

Concatenating Multiple Input Strings Using CONCAT() Function

In this example, we'll have several input strings to get a resultant sentence.

Output:

Concatenating All the Arguments Along With a NULL Value

Output:

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:

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:

Concatenating Numeric String Using CONCAT() Function

Numeric strings can also be concatenated with the CONCAT() function.

Output:

Concatenating a String With Another Function

We have seen previously how a string can be concatenated with another function. Here's another example:

Output:

Concatenating a String With Other Data Types

A string can be concatenated with other data types using MySQL's CONCAT() function.

Output:

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:

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:

Concatenating a String With a Subquery

See Also

• MySQL Count
• MySQL current date
• MySQL DATEDIFF()
• Coalesce MySQL