MySQL concat函数

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

原文链接：http://outofmemory.cn/mysql/function/sql-concat-in-mysql