title: Combine First, Middle Initial, Last name and Suffix in T-SQL (No extra spaces)
body: <p>I'm trying not to reinvent the wheel here...I have these four fields:</p>
  
  <pre><code>[tbl_Contacts].[FirstName],
  [tbl_Contacts].[MiddleInitial],
  [tbl_Contacts].[LastName],
  [tbl_Contacts].[Suffix] 
  </code></pre>
  
  <p>And I want to create a <code>FullName</code> field in a view, but I can't have extra spaces if fields are blank...</p>
  
  <p>So I can't do <code>FirstName + ' ' + MiddleInitial + ' ' + LastName + ' ' + Suffix...</code> Because if there is no middle initial or suffix I'd have 2 extra spaces in the field.  I think I need a Case statement, but I thought someone would have a handy method for this...Also, the m<code>iddleinitial</code> and <code>suffix</code> may be null.</p>