title: Correct Amount of Spaces with concatenated person's name in SQL Server
body: <p>Here is my SQL as I have it now.  The first and last name should never be missing because they are required on the interface with validation.  However, if the middle initial is missing, I don't want two spaces in the result; I just want one space.  I could put a case statement in, but that seems like overkill if SQL already has a function for this purpose.  Does anyone know if there is a function for this purpose?  What would the code look like?</p>
  
  <pre><code>SELECT ISNULL(contact.firstname, '') 
           + ' ' 
           + ISNULL(contact.middleinitial, '') 
           + ' ' 
           + ISNULL(contact.lastname, '')
  FROM dbo.contact
  </code></pre>