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

Question

I'm trying not to reinvent the wheel here...I have these four fields:

[tbl_Contacts].[FirstName],
[tbl_Contacts].[MiddleInitial],
[tbl_Contacts].[LastName],
[tbl_Contacts].[Suffix] 

And I want to create a FullName field in a view, but I can't have extra spaces if fields are blank...

So I can't do FirstName + ' ' + MiddleInitial + ' ' + LastName + ' ' + Suffix... 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 middleinitial and suffix may be null.

Answer 1

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.