Gary Synan

NULLS Are Funny Animals

Est. Reading Time: 4 minutes

Nulls are funny animals that require special handling.  They are not even like empty strings that have a zero length and nothing for a value.  Nulls have no value, undefined.

When you access data from a DB table and return it to your application code you try to keep the DB nulls from returning to the code.  If you don’t it is a sure error waiting to blow up your page.  A Common method for preventing  that is to replace the DB null with an empty string in the SELECT statement. 

I have used the following technique often.  Let’s assume we have DB column named ‘flag’ that we are concerned with. The select statement may look like this: 

                SELECT ID,                                 FirstName,                                 LastName,                                 .                                 .                                 .                                 isnull(Flag, ‘’)  as Flag                 FROM …                              

In the example above, if the flag was null, an empty string would be returned  and   I would check in my code something like this:  If flag <> “” then …  to see if there was any data in the field.  However I found out the hard way that the DB column has to be defined so that it can pass an empty string.  Varchar allows this to happen since it can have a length of 0.  I ran into a situation where my application code check for an empty string was false but when I checked the DB field it was null.  So why wasn’t it passing and empty string?  Obviously there was a bug or some type of corruption in the DB because I use this technique all the time. 

After quite some effort in debugging, I discovered that the column of flag was  defined as a char(1). No problem, because I indicated in the select statement to pass me an empty string if the field was null.  I may have indicated to pass me an empty string but that is not what happens when the column has a defined length.  I got passed back a 1 character field of a space because the column was defined to be 1 character and it got space padding.   

Lessons learned.  If I indicate in the select statement to pass a null value back as an empty string, I make sure the column does not have a defined length but rather a varchar designation so it can have a 0 length to reflect the empty string.

Nulls are funny animals.