mysql之使用 SQL concat 和 coalesce 在值为 null 时显示

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

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

我想返回格式化为单个值的城市、州和邮政编码，如下所示:

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 

我一直在尝试类似以下的方法，但我认为我做错了。谢谢

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

请您参考如下方法:

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

或 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