How to exclude NULL values inside CONCAT MySQL?

If I have this - tadd is the Address table:

CONCAT(tadd.street_number, ' ',
            tadd.street_name,', ',
            tadd.apt_number,', ',
            tadd.city,', ',
            tadd.postal_code,', ',
            tadd.country) AS 'Address'

Is there a way to exclude the apt_number if it doesn't exist?

I was thinking of:

WHERE tadd.apt_number IS NOT NULL

But it will return only those rows with apt_number, and even if something works how do I then deal with that extra comma.

If it's a duplicate please post a link in comments.

If you want to skip NULL values (but not empty strings), you can use CONCAT_WS() function:

CONCAT_WS( ', ',            -- Separator
           CONCAT_WS(' ', tadd.street_number, tadd.street_name),
           tadd.apt_number,  tadd.city, 
           tadd.postal_code, tadd.country
         ) AS Address

From the docs:

CONCAT_WS(separator,str1,str2,...)

CONCAT_WS() stands for Concatenate With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments. If the separator is NULL, the result is NULL.

CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.

StackExchange Database Administrators Q#110949, answer score: 30