Using ISNULL in SQL Server to Replace NULL Values

There are different ways to handle NULL’s and I wrote about one such way in my last post. In this post, we look at SQL ISNULL() and specifically at the SQL Server version. MySQL does have this function but it behaves a little differently as it returns a boolean value.

Let’s look at the syntax

ISNULL(check_expression, replacement_value)

check_expression – this is the expression to be checked. This can be any data type.

replacement_value – this is the value to replace the NULL with. This can also be a field.

The data type returned depends on what check_expression data type is. Pass in a varchar field here and a varchar will be returned. A varchar will be returned by ISNULL() here even if replacement_value has an integer value passed in. However, there are exceptions as the replacement_value has to be of a data type that can be implicitly converted to the same data type as check_expression.

If check_expression is NULL then the data type returned will be whatever data type is supplied as replacement_value

Let’s take a look at an example.

SELECT HomePhone 
FROM Leads;

SQL ISNULL example before

So this is the data with some NULL’s in rows 2 and 3. Now we will apply ISNULL() to return a hyphen in place of the NULL.

SELECT ISNULL(HomePhone, '-') AS Phone
FROM Leads;

SQL ISNULL example after

If we now pass in an integer as replacement_value, the code will still work fine because the integer can be implicitly converted to a varchar.

SELECT ISNULL(HomePhone, 1) AS Phone
FROM Leads;

SQL ISNULL implicit conversion example

So this works fine. Personally if I were writing this code, I would implicitly convert it within the SQL. In my opinion, this reads better because it’s clear what the data type returned is.

SELECT ISNULL(HomePhone, CAST(1 AS VARCHAR)) AS Phone
FROM Leads;

I would write it in the same way if replacement_value was a field for the same reason.

SELECT ISNULL(HomePhone, CAST(ReplacementField AS VARCHAR)) AS Phone FROM....

 

 

 

 

It's only fair to share...Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInPin on PinterestBuffer this pageShare on RedditShare on StumbleUpon
About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on

Speak Your Mind

*

*