Computed column (COALESCE vs CASE vs ISNULL)

Question

I recently posted a similar question, and now that I need to update this code, I ask the next question again. The previous question is here:
Computed column based on nullable columns

My data ( Address1, Address2, City, State, Zip, Country ) may have incomplete information. That is, I cannot guarantee that everyone except the State and Country columns will have data.

I would like to have computed columns for FullAddress .

I used to use COALESCE , which worked fine if all the fields are filled. Now that the data requirements have been relaxed, this is no longer an option (because we end up with repeated commas in FullAddress ), this is what I used earlier (note, I just work with SELECT statements here for ease of use - it converts the "alter operator to calculated columns) table add "when I have something that works for all cases):

 SELECT (((((COALESCE([Address1],'') + COALESCE(', '+[Address2],'')) + COALESCE(', '+[City],'')) + COALESCE(', '+[State],'')) + COALESCE(', '+[Zip],'')) + COALESCE(', '+[Country],'')) AS FullAddress FROM Locations 

Now I have put together an alternative using CASE , but it still does not work for the case with the edge where Address1 is NULL (the problem is that FullAddress will have a "," like the first two characters)

 SELECT CASE WHEN [Address1] IS NOT NULL THEN [Address1] ELSE '' END + CASE WHEN [Address2] IS NOT NULL THEN ', ' + [Address2] ELSE '' END + CASE WHEN [City] IS NOT NULL THEN ', ' + [City] ELSE '' END + CASE WHEN [State] IS NOT NULL THEN ', ' + [State] ELSE '' END + CASE WHEN [Zip] IS NOT NULL THEN ', ' + [Zip] ELSE '' END + CASE WHEN [Country] IS NOT NULL THEN ', ' + [Country] ELSE '' END AS [FullAddress] FROM Locations 

I am a bit stuck at this point. Any recommendations what to try next?

Answer 1

you can use this template:

 SELECT ISNULL(Address1 + ', ', '') + ISNULL(Address2 + ', ', '') + ISNULL(City + ', ', '') -- .... AS FullAddress 

The result of concatenation NULL + ', ' is NULL => Address1 + ', ' will be NULL or a valid address => ISNULL(Address1 + ', ', '') will be an empty string or a valid address.

Answer 2

 SELECT STUFF( COALESCE(', ' + Address1, '') + COALESCE(', ' + Address2, '') + ... 1, 2, '' ) AS FullAddress FROM Locations 

The concatenated string will either be empty or begin with , (comma and space). STUFF() will remove the first two characters and return the rest of the string.