How to avoid space while concatenation if database values are null?

Question

I am concatenating three database values Firstname,middlename,lastname.Here is the Query -

Select ISNULL(t.FirstName,'')+' '+ISNULL(t.MiddleName,'')+' '+ ISNULL(t.LastName,'') as [UserName],Email from table1 t

I am getting null values for some middle names. Thats why I am getting extra spaces after concatenation.

Firstname LastName --getting two spaces at place of one

I want if middle name is null then only one space will be present in result.If it not null then there is one space between firstname, middlename and lastname. I have tried some methods to avoid this but nothing worked.

Answer 1

Put the space inside ISNULL function:

Select ISNULL(t.FirstName + ' ', '') 
    + ISNULL(t.MiddleName + ' ', '')
    + ISNULL(t.LastName,'') as [UserName]