Conditional CONCAT with potentially NULL or empty values

Question

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.

Answer 1

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) ) 

Answer 2

Have a look at the CONCAT_WS function. It does exactly what you want.