使用SQL concat和coalesce来显示值为null时

I have the following table. city, state, and zipcode are all varchar type and can be NULL.

我有下表。 city,state和zipcode都是varchar类型,可以为NULL。

MyContacts
-id (int PK)
-city (varchar null)
-state (varchar null)
-zipcode (varchar null)

I would like to return city, state, and zipcode formatted as a single value as follows:

我想将城市,州和邮政编码格式化为单个值,如下所示:

Seattle WA 98423 if all are not NULL
Seattle WA if zip is NULL
Seattle 98423 if state is NULL
WA 98423 if city is NULL
Seattle if state and zip is NULL
WA if city and zip is NULL
98423 if city and state is NULL
NULL if all of them are NULL

I have been trying something like the following, but think I am going about it wrong. Thanks

我一直在尝试类似下面的事情,但我想我错了。谢谢

SELECT COALESCE(CONCAT(city," ",state," ",zipcode),CONCAT(city," ",states),zipcode) AS location

2 个解决方案

#1

CASE WHEN COALESCE(city,state,zip_code) IS NOT NULL
THEN
    TRIM(REPLACE(CONCAT(IFNULL(city,""),
                   " ",
                   IFNULL(state,""),
                   " ",
                   IFNULL(zip code,"")),
            "  "," "))
ELSE
    NULL
END

OR CONCAT_WS already handles NULL effectively, so we don't need a NULL check inside it.

OR CONCAT_WS已经有效地处理NULL,因此我们不需要在其中进行NULL检查。

CASE WHEN COALESCE(city,state,zip_code) IS NOT NULL
THEN
    CONCAT_WS(' ',city,
                  state,
                  zipcode) 
ELSE
    NULL
END AS location

#2

Maybe this?

SELECT CONCAT_WS(' ',COALESCE(city,''),COALESCE(state,''),COALESCE(zipcode,'')) AS location