MySQL 具有潜在 NULL 或空值的条件 CONCAT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7751597/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Conditional CONCAT with potentially NULL or empty values
提问by Houdmont
In the below piece of code, I am creating an Address field by concatenating various parts of an address.
在下面的一段代码中，我通过连接地址的各个部分来创建一个地址字段。
However, if for instance address2was empty, the trailing ,will still be concatenated into Address.
但是，如果 instanceaddress2为空，则尾随,仍将连接到 Address。
This means if all fields were empty, I end up with a result of ,,,,.
这意味着如果所有字段都为空，我最终会得到,,,,.
If address1is "House Number"and everything else is empty, I end up with House Number,,,,.
如果address1是"House Number"而其他一切都是空的，我最终会得到House Number,,,,.
CONCAT( COALESCE(address1,'') , ', ' ,
COALESCE(address2,'') , ', ' ,
COALESCE(address3,'') , ', ' ,
COALESCE(city,'') , ', ' ,
COALESCE(zip, '')
) AS Address,
Is there some way of conditionally placing the commas between address parts only if the content of an address part is not empty.
仅当地址部分的内容不为空时，是否有某种方法有条件地将逗号放在地址部分之间。
Such as something along the lines of (pseudo-code) IF(address1) is NULL use '' ELSE use ','
例如（伪代码） IF(address1) is NULL use '' ELSE use ','
Thank you.
谢谢你。
回答by Martin
CONCAT_WS(', ',
IF(LENGTH(`address1`),`address1`,NULL),
IF(LENGTH(`address2`),`address2`,NULL),
IF(LENGTH(`address3`),`address3`,NULL),
IF(LENGTH(`city`),`city`,NULL),
IF(LENGTH(`zip`),`zip`,NULL)
)
回答by Mat
回答by Bouke Versteegh
As answered here: How to concat_ws multiple fields and remove duplicate separators for empty slots
如此处回答：如何连接多个字段并删除空插槽的重复分隔符
This will clear out empty strings as well as NULL values.
这将清除空字符串以及 NULL 值。
CONCAT_WS(", ", NULLIF(address1, ""), NULLIF(address2, ""), NULLIF(address3, ""), NULLIF(city, ""), NULLIF(zip, ""))
回答by Bojangles
Using CONCAT_WSas Mat says is a very good idea, but I thought I'd do it a different way, with messy IF()statements:
CONCAT_WS像 Mat 所说的那样使用是一个很好的主意，但我想我会用不同的方式来做，用凌乱的IF()语句：
CONCAT( COALESCE(address1,''), IF(LENGTH(address1), ', ', ''),
COALESCE(address2,''), IF(LENGTH(address2), ', ', ''),
COALESCE(address3,''), IF(LENGTH(address3), ', ', ''),
COALESCE(city,''), IF(LENGTH(city), ', ', ''),
COALESCE(zip,''), IF(LENGTH(address1), ', ', ''),
) AS Address,
The IF()s check if the field has a length and if so returns a comma. Otherwise, it returns an empty string.
该IF()支票如果该字段的长度，如果是返回一个逗号。否则，它返回一个空字符串。
回答by diEcho
回答by Imran Ahmad
As it is clearly mentioned in the docthat:
正如文档中明确提到的那样：
CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.
CONCAT_WS() 不会跳过空字符串。但是，它会跳过分隔符参数之后的任何 NULL 值。
We can remove empty spaces by NULLwhich is elegantly handled by CONCAT_WS.
我们可以通过 NULL优雅地处理NULL 删除空格CONCAT_WS。
CONCAT_WS(', ',
IF(`address1` != '', `address1`, NULL),
IF(`address2` != '', `address2`, NULL),
IF(`address3` != '', `address3`, NULL),
IF(`city` != '', `city, NULL),
IF(`zip` != '', `zip, NULL)
)
