将多个字段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屋!