Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - ISNULL() - Replacing NULL Values in Expressions

By: FYIcenter.com

(Continued from previous topic...)

How To Replace NULL Values in Expressions using ISNULL()?

As you learned from provious tutorials, NULL values presented in expressions will cause the final results to be NULL. Sometimes, you want NULL values to be replaced with some default values, like 0, '', or 'NULL', so that expressions can be evaluated properly.

SQL Server offers a built-in function called ISNULL() to help you replacing NULL values in expressions:

ISNULL(expression, replacement) 
-- Returns "expression", if it is not NULL
-- Returns "replacement", if "expression" is NULL

The tutorial example below shows you how to replace possible NULL values in @middle_initial:

USE FyiCenterData;
GO

CREATE PROCEDURE welcome
  @first_name VARCHAR(20),
  @middle_initial VARCHAR(1),
  @last_name VARCHAR(20)
AS
  PRINT 'Hello '+@first_name
    + ' '+@middle_initial
    + ' '+@last_name;
  PRINT 'Hello '+@first_name
    + ' '+ISNULL(@middle_initial,'')
    + ' '+@last_name;
GO

EXEC welcome 'John', 'W', 'King';
GO
Hello John W King
Hello John W King

EXEC welcome 'John', NULL, 'King';
GO
 
Hello John  King

The first PRINT statement in the second test returns a blank line because of NULL value.

(Continued on next topic...)

1. What Are NULL Values?
2. How To Assign NULL Values to Variables or Columns?
3. What Happens If NULL Values Are Involved in Arithmetic Operations?
4. What Happens If NULL Values Are Involved in String Operations?
5. What Happens If NULL Values Are Involved in Datetime Operations?
6. What Happens If NULL Values Are Involved in Bitwise Operations?
7. What Happens If NULL Values Are Involved in Comparison Operations?
8. What Happens If NULL Values Are Involved in Boolean Operations?
9. How To Test NULL Values Properly?
10. How To Replace NULL Values in Expressions using ISNULL()?
11. How To Replace Given Values with NULL using NULLIF()?