我有下表。城市,州和Zipcode都是varchar类型,可以为null。
MyContacts
-id (int PK)
-city (varchar null)
-state (varchar null)
-zipcode (varchar null)
我想返回作为单个值的城市,州和Zipcode,如下:
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
已经有效地处理无效,因此我们不需要内部检查。
CASE WHEN COALESCE(city,state,zip_code) IS NOT NULL
THEN
CONCAT_WS(' ',city,
state,
zipcode)
ELSE
NULL
END AS location
也许这个?
SELECT CONCAT_WS(' ',COALESCE(city,''),COALESCE(state,''),COALESCE(zipcode,'')) AS location
尝试此
SELECT COALESCE(CONCAT(city , '', state, '', zipcode), CONCAT(city, '', state), zipcode) AS location from MyContacts;