In the below piece of code, I am creating an Address field by concatenating various parts of an address.
However, if for instance address2 was empty, the trailing , will still be concatenated into Address.
This means if all fields were empty, I end up with a result of ,,,,.
If address1 is "House Number" and everything else is empty, I end up with 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 ','
Thank you.
14 Answers
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)
)Using CONCAT_WS as Mat says is a very good idea, but I thought I'd do it a different way, with messy IF() statements:
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.
try with MAKE_SET
SELECT MAKE_SET(11111,`address1`,`address2`,`address3`,`city`,`zip`) AS AddressIt will returns a string with all NOT NULL value separated by ,
CONCAT_WS(', ', NULLIF(`address1`,''), NULLIF(`address2`,''), NULLIF(`address3`,''), NULLIF(`city`,''), NULLIF(`zip`,'')
)- CONCAT_WS combines non-NULL strings.
- NULLIF writes NULL if left and right side are equals. In this case if values are equals an empty sting ''.
