» MySQL Functions » SQL Concatenate Functions in MySQL

SQL Concatenate Functions in MySQL

Summary: In this tutorial, you will learn various ways to concatenate two or more string together by using concat function which is provided by MySQL.

Almost RMDMSs support us to concatenate two or more strings together by using different implementations. MS SQL server us operator plus (+) to concatenate strings. Oracle only allows you to concatenate two strings with concat function or operator ||. MySQL supports a more flexible way by enabling us to concatenate more than two strings or even concatenate strings with predefined separator. Let's start with each concatenate function.

Concat Function

CONCAT(str1,str2,...)

The concat function is used to concatenate two or more string and returns concatenating string. If the arguments are numeric, they will be converted to strings before concatenating. If any of argument in the argument list is NULL, the concat function will return NULL.

Here are several examples to demonstrate the concat function. We will use the sample database to practice.

In order to display the first 5 full name of contacts of the customers we use concat function to concatenate the first name and last name and a separator between them. Here is the query:

 SELECT CONCAT(contactLastname,', ',contactFirstname) fullname
 FROM customers
 LIMIT 5

The output result is

fullname          
------------------
Schmitt, Carine   
King, Jean        
Ferguson, Peter   
Labrune, Janine   
Bergulfsen, Jonas

Concat with Separator Function

MySQL also supports concat_ws function which allows us to concatenate two or more than two strings with a predefined separator. The syntax of the concat_ws function is:

 CONCAT_WS(seperator,str1,str2,...)

The first parameter is the predefined separator you specified and the others are the string you want to concatenate. the result is the concatenating string with separator between each. For example, you can achieve the same result in the above example by using concat_ws function instead of concat function.

 SELECT CONCAT_WS(', ',contactLastname,contactFirstname) fullname
 FROM customers
 LIMIT 5

Here is another example of using concat_ws to get address format of customers.

 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
 LIMIT 2

Here is the output result:

Customer_Address                                   
---------------------------------------------------
Schmitt Carine                                     
54, rue Royale                                     
44000 Nantes                                       
France                                             
                                                   
King Jean                                          
8489 Strong St.                                    
83030 Las Vegas                                    
USA
Search