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.