concatenating strings and nulls in SQL

let's start with a table from scratch

create table test(
firstname varchar(10) null,
lastname varchar(10) null
)

we then insert some data (sorry about people with these names)

insert into test values ('john', 'smith')
insert into test values ('john', 'doe')
insert into test values (null, 'perez')

we're ready to concatenate some strings:

select lastname + ' ' + firstname
from test

but we get:

1 smith john
2 doe john
3 NULL

what happened?

in SQL, when you concatenate strings, if one of the strings is null, the result of the concatenation is null.

At first you might think this is bad, but it can actually help you more than it hurts, you just have to know the trick, so what do we do to fix the query?

select isnull(lastname, '') + ' ' + isnull(firstname, '')
from test

now we get:

1 smith john
2 doe john
3 perez

ok, but how is that helpful?

well, let's suppose you want this format:

lastname, first name

but only if there is a first name, if the first name is null you just want the last name with no comma

let's say there is a rule on the database that the last name cannot be null, so we can write the query as:

select lastname + isnull(', '+firstname, '')
from test

and we get:

1 smith, john
2 doe, john
3 perez

see, I concatenated the comma with the first name, and if the first name is null, the the comma gets eliminated as well

Just one more trick for your bag of tricks