MySQL CONCAT Function

 MySQL CONCAT Function

Summary: in this tutorial, you will learn various ways to concatenate two or more strings together by using the MySQL CONCAT and CONCAT_WS functions.

To concatenate two or more quoted-string values, you place the strings next to each other as the following syntax:

SELECT 'MySQL ' 'String ' 'Concatenation';

MySQL string concatenation is cleaner in comparison with other database management systems. For example, if you use PostgreSQL or Oracle, you have to use the string concatenation operator ||. In Microsoft SQL Server, you use the addition arithmetic operator (+) to concatenate string values.

Besides using spaces for string concatenation, MySQL provides two other functions that concatenate string values: CONCAT and CONCAT_WS.

MySQL CONCAT function

The MySQL CONCAT the function takes one or more string arguments and concatenates them into a single string. The CONCAT function requires a minimum of one parameter otherwise it raises an error.

The following illustrates the syntax of the CONCAT function.

CONCAT(string1,string2, ... );

The CONCAT the function converts all arguments to the string type before concatenating. If any argument is NULL, the CONCAT function returns a NULL value.

The following statement concatenates two quoted strings: MySQL and CONCAT.

SELECT CONCAT('MySQL','CONCAT');

If you add a NULL value, the CONCAT function returns a NULL value as follows:

SELECT CONCAT('MySQL',NULL,'CONCAT');

See the following customers the table in the sample database.

To get the full names of contacts, you use the CONCAT function to concatenate first name, space, last name as the following statement:

SELECT 
    concat(contactFirstName,' ',contactLastName) Fullname
FROM
    customers;

MySQL CONCAT_WS function: Concatenate strings with a separator

MySQL provides a special form of the CONCAT function: CONCAT_WS function. The CONCAT_WS function concatenates two or more string values with a predefined separator.

The following illustrates the syntax of the CONCAT_WS function:

CONCAT_WS(seperator,string1,string2, ... );

The first argument is the separator for other arguments: string1, string2, …

The CONCAT_WS the function adds the separator between string arguments and returns a single string with the separator inserted between string arguments.

The following statement concatenates two string values: John and Doe, and separates these two strings by a comma:

SELECT CONCAT_WS(',','John','Doe');

The CONCAT_WS function returns NULL if and only if the first argument, which is the separator, is NULL. See the following example:

SELECT CONCAT_WS(NULL ,'Jonathan', 'Smith');

Unlike the CONCAT function, the CONCAT_WS function skips NULL values after the separator argument. In other words, it ignores NULL values.

SELECT CONCAT_WS(',','Jonathan', 'Smith',NULL);

The following statement constructs complete addresses using the CONCAT_WS function:

 SELECT 
    CONCAT_WS(CHAR(13),
            CONCAT_WS(' ', contactLastname, contactFirstname),
            addressLine1,
            addressLine2,
            CONCAT_WS(' ', postalCode, city),
            country,
            CONCAT_WS(CHAR(13), '')) AS Customer_Address
FROM
    customers;

Here is the output result:

Customer_Address                                   
---------------------------------------------------
Schmitt Carine                                     
54, rue Royale                                     
44000 Nantes                                       
France                                             

King Jean                                          
8489 Strong St.                                    
83030 Las Vegas                                    
USA

...

In this tutorial, you have learned how to use MySQL CONCAT and CONCAT_WS functions to concatenate one or more string values into a single string.