Skip to content

SQL ISNULL

June 10, 2009

I know this is not a super-advanced topic.  I know this is not that difficult.  I just hope that if someone new to SQL is looking for help, this may provide exactly that.

I use this all the time and if you are new to SQL, this is an important thing to learn as NULL values can kill your queries.

Most recently, I had to take a first name from a table, a last name from a table, and join them together in reverse order using a comma as the delimiter. This is easy enough to do in SQL:

SELECT LastName + ‘, ‘ + FirstName FROM Table

This will return:

Petersen, Rick
Doe, John

If you don’t have a value in one of the fields however, this will blow up. Let’s say we didn’t know John’s last name… We might expect to at least see the row and get something like:

Petersen, Rick
, John

But instead, we will get:

Petersen, Rick

The null will disrupt the query. To get what I said we might have expected above, we would do:

SELECT ISNULL(LastName,’’) + ‘, ‘ + ISNULL(FirstName,’’) AS FullName FROM Table

ISNULL says, if it IS NULL, replace it with the value following… in this case the empty string… we will now get the expected result.

The issue I ran into was that we had many records, and in the large majority of them, both FirstName and LastName were NULL and I was using the above code, so we had records like:

Petersen, Rick
,
,
,
,
,
Doe, John
,
,

That looked horrible on the report, so I needed the commas to go away if the LastName field was NULL. This gets us into CASE statements which I think deserve their own post, but for here I’ll just gloss over them:

SELECT ISNULL(LastName,’’) + CASE ISNULL(LastName,”) WHEN ” THEN ” ELSE ‘, ‘ END + ISNULL(FirstName,’’) AS FullName FROM Table

This will make sure we get a record returned for every record in the database whether the names are null or not, and it will not return just a comma if the fields are blank. Otherwise you will get ‘LastName, FirstName’ in a column titled ‘FullName’.

Advertisements
No comments yet

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: