How to concatenate strings and commas in SQL Server?

Question

I'm relatively new to MSSQL, so sorry if the question might sounds trivial. I want to concatenate multiple fields with a delimiter ,. However, when the field is empty, the extra , will be included in the result string as well. So is there an easy way to solve this problem? For example,

SELECT VRI.Street_Number_and_Modifier + ',' + 
       VRI.Street_Direction + ',' + 
       VRI.Street_Name + ',' + 
       VRI.Street_Direction + ',' + 
       VRI.Street_Suffix + ',' + 
       VRI.Street_Post_Direction + ',' + 
       VRI.Unit
FROM View_Report_Information_Tables VRI

Answer 1

This modified version of Lamak's handles NULL or strings containing only space/empty:

SELECT  COALESCE(NULLIF(VRI.Street_Number_and_Modifier, '') + ',', '') + 
        COALESCE(NULLIF(VRI.Street_Direction, '') + ',', '') + 
        COALESCE(NULLIF(VRI.Street_Name, '') + ',', '') + 
        COALESCE(NULLIF(VRI.Street_Direction, '') + ',', '') + 
        COALESCE(NULLIF(VRI.Street_Suffix, '') + ',', '') + 
        COALESCE(NULLIF(VRI.Street_Post_Direction, '') + ',', '') + 
        COALESCE(NULLIF(VRI.Unit, ''), '')
FROM View_Report_Information_Tables VRI