将多个字段CONCAT到一个字段中，并以单个行距隔开 [英] CONCAT multiple fields to a single field, single spaced

问题描述

我正在尝试连接first middle maiden和last名称字段，并使用它们来更新名为fullname

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

对于每个用户，可以填写这4个字段的任意组合.从0到全部4. 但是，我还需要在每个名称之间使用一个空格(而不是多个空格).

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).

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

推荐答案

MySQL具有CONCAT_WS-与分隔符连接

MySQL has CONCAT_WS - concatenate with separator

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

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

正如下面的andr所指出的，请确保任何串联的字段都包含NULL而不是空字符串('')，否则您将在输出中得到一个双倍空格.

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.

提琴: http://sqlfiddle.com/#!2/1fe83/1

因此请小心，如果将来使用此功能创建一个小的CSV列表，因为您不会获得NULL字段的逗号.您必须对每个可为空的列做一个COALESCE(column, '')包装器.

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.

这篇关于将多个字段CONCAT到一个字段中，并以单个行距隔开的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！