CONCAT_WS('', -- hack, empty delimiter
tadd.street_number, ' ',
tadd.street_name,', ',
CONCAT(tadd.apt_number,', '), -- hack, this line will become NULL, when apt_number is null, and will be omitted with delimiter
tadd.city,', ',
tadd.postal_code,', ',
tadd.country) AS 'Address'
I don't know mysql, but in MSSQL (TQSL) the solution looks like:
SELECT
tadd.street_number + ' ' +
tadd.street_name + ', ' +
ISNULL(tadd.apt_number + ', ', '') +
tadd.city + ', ' +
tadd.postal_code + ', ' +
tadd.country AS 'Address'
Moreover, you can omit all NULL
fields, not just apt_number:
SELECT CONCAT_WS(', ',
CONCAT(tadd.street_number, ' ', tadd.street_name),
tadd.apt_number,
tadd.city,
tadd.postal_code,
tadd.country) AS 'Address'