• Skip to main content
  • Skip to primary sidebar

DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

Using ISNULL in SQL Server to Replace NULL Values

May 11, 2017 by Andy Hayes Leave a Comment

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....

 

 

 

 

Related Posts:

  • How to fix "conversion failed when converting date and/or time from character string"
    How to fix "conversion failed when converting date…
  • sql grouping sets
    Using SQL GROUPING SETS for Multiple GROUP BY…

Filed Under: All Articles, SQL Tips and Tricks Tagged With: sql server, t-sql

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 Google+

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Categories

  • All Articles (84)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (21)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to shrink tempdb
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to fix “conversion failed when converting date and/or time from character string”

Recent Posts

  • How to fix “conversion failed when converting date and/or time from character string”
  • Using SQL GROUPING SETS for Multiple GROUP BY Queries in a Single Query
  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial

Search

Connect

  • Twitter
  • Facebook
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright ©