In our Property table we have addresses in different fields. A full address field is used for the ease of report generation. This field is a concatenation of the different address fields. Sometimes the fields are NULL or empty. I use the following code to create CSV string:
select STUFF( coalesce(',',nullif(PROPNUM,''),'') + coalesce(','nullif(STREETDSC,''),'') + coalesce(','nullif([LOCALITY1],''),'') + coalesce(',',nullif([LOCALITY2],''),'') + coalesce(',',nullif([POSTCODE],''),'') from tmppropertyimport
I am using COALESCE and NULLIF for this purpose.
In short COALESCE returns the first non-null expression from its parameter(s).
NULLIFF returns a null value if the two specified expressions are equal.
Lets look at how it works.
NULLIF(PROPNUM, ‘’) – returns PROPNUM if not null or not empty. Returns NULL if PROPNUM is NULL or empty.
‘,’ + NULLIF(PROPNUM, ‘’) – returns “,PROPNUM” if PROPNUM has data. Returns NULL if PROPNUM is null or empty.
The result is CSV string beginning with a comma. I get rid of that comma using STUFF(string,1,1,’’).
Thanks for this goes to Stackoverflow post: