Combine First, Middle Initial, Last name and Suffix in T-SQL (No extra spaces)

Assuming that all columns could be nullable, you can do something like:

RTrim(Coalesce(FirstName + ' ','') 
+ Coalesce(MiddleInitial + ' ', '')
+ Coalesce(LastName + ' ', '')
+ Coalesce(Suffix, ''))

This relies on the fact that adding to a NULL value yields a NULL.

Whichever options you choose, here's something to think about: this will be a rather involved and thus time consuming option, especially if you have it in a view which gets evaluated each and every time for each and every row in question.

If you need this frequently, I would recommend you add this to your base table as a persisted, computed field - something like:

ALTER TABLE dbo.tbl_Contacts
    ADD FullName AS  (insert the statement of your choice here) PERSISTED

When it's persisted, it becomes part of the underlying table, and it's stored and kept up to date by SQL Server. When you query it, you get back the current value without incurring the cost of having to concatenate together the fields and determine which to use and which to ignore...

Just something to think about - something that too many DBA's and database devs tend to ignore and/or not know about....

You may want to pass the FirstName + ' ' + MiddleInitial + ' ' + LastName + ' ' + Suffix concatenation through the REPLACE() function in order to substitute duplicate spaces into a single space.

REPLACE(FirstName + ' ' + MiddleInitial + ' ' + LastName + ' ' + Suffix, '  ', ' ')
--                                                                        --    -

---

EDIT:

Just noticed that some of your fields may be NULL, and therefore the above would not work in that case, as the whole string would become NULL. In this case, you can use the COALESCE() method as suggested by Thomas, but still wrapped it in a REPLACE():

REPLACE(RTRIM(COALESCE(FirstName + ' ', '') +
              COALESCE(MiddleInitial + ' ', '') +
              COALESCE(LastName + ' ', '') +
              COALESCE(Suffix, '')), '  ', ' ')

Test:

SELECT REPLACE(RTRIM(COALESCE('John' + ' ', '') +
                     COALESCE('' + ' ', '') +
                     COALESCE('Doe' + ' ', '') +
                     COALESCE(NULL, '')), '  ', ' ')

-- Returns: John Doe