An annoying problem with Nulls

I just lost half an hour of my life which I’ll never get back on this. Don’t make the same mistake. I extracted some customer details for a mailing (it’s OK, they ticked the box that they want to hear about our special offers) but Marketing complained that some of the address data contained nulls. I checked the spreadsheet I’d given them and they were right. So I checked my query and followed the trail back to the source record for an example. The value in AddressLine2 was Null. But my select statement said isnull(AddressLine2, ”) as AddressLine2 and it was returning Null. WTF?! The sharp-eyed reader has probably already noticed that the value was the string ‘Null’, not the empty value NULL. I got there in the end but it took a while. Good job it wasn’t upper case or I might have had to ask one of the developers for help!

If you get this problem and it’s upper case you could try using the lower() function which will change the case of ‘NULL’ but not NULL:

declare @a varchar(10) = null;
declare @b varchar(10) = 'NULL';
select lower(@a) as a, lower(@b) as b;

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s