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;
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;
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;
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....
Leave a Reply