title: Computed Column (COALESCE vs CASE vs ISNULL)
body: <p>I posted a similar question a while back and now as I need to update this code, I am back to ask a follow-on question.  Previous question is here:<br>
  <a href="https://stackoverflow.com/questions/6590891/computed-column-based-on-nullable-columns">Computed column based on nullable columns</a></p>
  
  <p>My data (<code>Address1, Address2, City, State, Zip, Country</code>) may have incomplete information.  I.e. I can't be guaranteed that anything other than <code>State</code> and <code>Country</code> columns will have data.</p>
  
  <p>I would like to have a computed columns for <code>FullAddress</code>.</p>
  
  <p>Previously, I used <code>COALESCE</code>, which worked great if all fields are filled in.  Now, as the data requirements have been relaxed, this is no longer an option (because we end with repeated commas in the <code>FullAddress</code>).  Here is what I have been using previously (<em>note, I'm just working with SELECT statements here for ease of use - will convert into a computed columns "alter table add" statement once I have something that works for all cases</em>):</p>
  
  <pre><code>SELECT (((((COALESCE([Address1],'')
      + COALESCE(', '+[Address2],''))
      + COALESCE(', '+[City],''))
      + COALESCE(', '+[State],''))
      + COALESCE(', '+[Zip],''))
      + COALESCE(', '+[Country],'')) AS FullAddress
  FROM Locations
  </code></pre>
  
  <p>Now, I have put together an alternative using <code>CASE</code>, but it still doesn't work for the edge case where <code>Address1 is NULL</code> (the problem is that the <code>FullAddress</code> will have ', ' as the first two characters)</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I'm a little stuck at this point.  Any recommendations what to try next?</p>