MySQL CONCAT Function: Concatenate Two or More Strings

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

To concatenate two or more quoted string values, place the string side by side as follows:

SELECT ‘MySQL’ ‘String’ ‘Concatenation’; Code language: SQL (Structured Query Language) (sql)

Try it

MySQL’s string concatenation is cleaner compared to other database management systems. For example, if you are using PostgreSQL or Oracle, you must use the string concatenation operator ||. In Microsoft SQL Server, you use the aggregation arithmetic operator (+) to concatenate string values.

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

Function

MySQL

CONCAT The MySQL CONCAT 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 generates an error.

The following is the syntax of the CONCAT function.

CONCAT(string1,string2, …) ); Code language: SQL (Structured Query Language) (sql)

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

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

SELECT CONCAT(‘MySQL’,’CONCAT’); Code language: SQL (Structured Query Language) (sql)

Try it

If you add a null value, the

CONCAT function returns a null value as follows:

SELECT CONCAT(‘MySQL’,NULL,’CONCAT’); Code language: SQL (Structured Query Language) (sql)

Try it See

the following

table of customers in the sample database.

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

SELECT concat(contactFirstName,’ ‘,contactLastName) Full name OF customers; Code language: SQL (Structured Query Language) (sql)

Try it

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 is the syntax of the CONCAT_WS function:

CONCAT_WS(separator,string1,string2, …); Code language: SQL (Structured Query Language) (sql)

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

The CONCAT_WS function adds the separator between the string arguments and

returns a single string with the separator inserted between the 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’); Code language: SQL (Structured Query Language) (sql)

Try it

The function CONCAT_WS 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’); Code language: SQL (Structured Query Language) (sql)

Try it Unlike

the

CONCAT function, the CONCAT_WS function ignores null values after the separator argument. In other words, ignore NULL values.

SELECT CONCAT_WS(‘,’,’Jonathan’, ‘Smith’,NULL); Code language: SQL (Structured Query Language) (sql)

Try it 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; Code language: SQL (Structured Query Language) (sql)

Try it

Here is the output result:

Customer_Address – Schmitt Carine 54, rue Royale 44000 Nantes France King Jean 8489 Strong St. 83030 Las Vegas USA … Code language: SQL (Structured Query Language) (sql)

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