将多个字段CONCAT到单个字段，单个间隔

I'm trying to concatenate a first middle maiden and last name fields and use that to update a single field which is named fullname

我正在尝试连接第一个中间的maiden和last name字段，并使用它来更新名为fullname的单个字段

For each user any combination of these 4 fields can be filled. From 0 to all 4. However I also need a single space between each name (not multiple spaces).

对于每个用户，可以填充这4个字段的任何组合。从0到全部4.但是我还需要在每个名称之间留一个空格（不是多个空格）。

UPDATE nameTable SET fullname = CONCAT(first, middle, maiden, last);

2 个解决方案

#1

MySQL has CONCAT_WS - concatenate with separator

MySQL有CONCAT_WS - 与分隔符连接

CONCAT_WS(' ', first, middle, maiden, last);

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws

As pointed out by andr below, make sure any concatenated fields contain NULL and not an empty string ('') otherwise you will get a double space in the output.

正如下面的andr所指出的，确保任何连接字段包含NULL而不是空字符串（''），否则您将在输出中获得双空格。

Fiddle: http://sqlfiddle.com/#!2/1fe83/1

小提琴：http：//sqlfiddle.com/#！2/1fe83/1

Further Application

Be careful therefore if in the future you use this function to make a small CSV list, because you won't get the comma for a NULL field. You'd have to do a COALESCE(column, '') wrapper around each nullable column.

因此，如果将来使用此函数创建一个小的CSV列表，请注意，因为您不会获得NULL字段的逗号。你必须围绕每个可以为空的列做一个COALESCE（列，''）包装器。

#2

empty string solution TRIM(BOTH ' ' FROM CONCAT_WS(' ', first, middle, maiden, last))

空字符串解决方案TRIM（BOTH''来自CONCAT_WS（''，第一，中间，处女，最后））

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_trim

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_trim