MySQL CONCAT() Function

The CONCAT() function allows you to concatenate two or more parameters.

CONCAT() returns the string that results from concatenating its parameters. The parameters don't necessarily need to be strings themselves. For example, if a parameter is numeric, it will be converted into a string as part of the concatenation (unless you use CAST() to prevent this).

Here's an example of a concatenation using CONCAT():

SELECT 

first_name, 

last_name,

   CONCAT(first_name, ' ', last_name)

FROM actor;

Result:

In the above example, we select the first_name and last_name columns from the actor table, and we also concatenate these two fields using CONCAT().

In this example, we add a space between the first name and last name, otherwise they'd be joined together with no space (eg, "NICK WAHLBERG" would end up as "NICKWAHLBERG"). We surround the space with single quotes (' ').

The space could have been any string.

Here's another example. This time we use a comma, and switch the first name and last name around:

SELECT 

first_name, 

last_name,

   CONCAT(first_name, ' ', last_name)

FROM actor;

Result:

If any Arguments are NULL

If any of the arguments are NULL, the CONCAT() function will return NULL.

Example:

In the above example, the third column concatenates the first two columns. When any of the first two columns contains a NULL value, the third column also results in NULL.

In case you're interested, this example uses a LEFT JOIN, which returns a NULL value on the right table (second column) if there's no match for the value on the left table (first column).

The CONCAT_WS() Function

You can use the CONCAT_WS() function (which stands for "Concatenate With Separator") to specify the separator that should be used. This is particularly handy if you've got a lot of fields to concatenate — saves you from adding a separator after each field.

SELECT 

last_name,

first_name, 

   CONCAT_WS(', ', last_name, first_name)

FROM actor;

Result:

The examples on this page use the Sakila sample database.