Handling Nulls When Concatenating

The problem with concatenating data such as names is that you often have to include columns with nulls.  A good example of this in the AdventureWorks2012 database is in the person table under the person schema.  If you want to get the full name out of the person table you may use a query like this:

select firstname + ' '+ middlename +' '+lastname as person
from person.Person

and this will return nulls in rows where any of the columns have a null:

There are a couple of solutions you can consider to resolve this.  One is to use the ISNULL function to let SQL Server know what to do with the nulls.

ISNULL

select firstname + ' '+ ISNULL(middlename,'')+' '+lastname as person
from Person.Person

For the second argument I have used '' to return an empty string in place of the null:

I could have put any value I wanted in place of the null eg:


select firstname + ' '+ ISNULL(middlename,'NO MIDDLENAME')+' '+lastname as person
from Person.Person

will put the string NO MIDDLENAME in place of null:

                                                       

You could use COALESCE to handle the nulls.

COALESCE

select FirstName + ' ' + coalesce(MiddleName,'') + ' ' + LastName as person
from person.Person

Like ISNULL, COALESCE allows you to pass an argument in to handle the nulls.
 

Also like ISNULL, COALESCE will also allow you to replace the null with any value you choose.