SQL Server ISNULL Function

Summary: in this tutorial, you will learn how to use the SQL Server ISNULL() function to replace NULL with a specified value.

SQL Server ISNULL() function overview

The SQL Server ISNULL() function replaces NULL with a specified value. The following shows the syntax of the ISNULL() function:

ISNULL(expression, replacement)
Code language: SQL (Structured Query Language) (sql)

The ISNULL() function accepts two arguments:

• expression is an expression of any type that is checked for NULL.
• replacement is the value to be returned if the expression is NULL. The replacement must be convertible to a value of the type of the expression.

The ISNULL() function returns the replacement if the expression evaluates to NULL. Before returning a value, it implicitly converts the type of replacement to the type of the expression if the types of the two arguments are different.

In case the expression is not NULL, the ISNULL() function returns the value of the expression.

SQL Server ISNULL() function examples

Let’s take some examples of using the ISNULL() function.

Using SQL Server ISNULL() function with the numeric data example

This example uses the ISNULL() function to return the second argument because the first argument is NULL:

SELECT 
    ISNULL(NULL,20) result;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

Result
-----------
20

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

Using SQL Server ISNULL() function with character string example

The following example uses the ISNULL() function to return the string 'Hello' because it is the first argument and not NULL:

SELECT 
    ISNULL('Hello', 'Hi') Result;
Code language: SQL (Structured Query Language) (sql)

The output is:

Result
------
Hello

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

Using SQL Server ISNULL() function to replace NULL values with meaningful values

First, create a new table named divisions that stores athlete’s divisions by ages:

CREATE TABLE divisions
(
    id      INT
    PRIMARY KEY IDENTITY, 
    min_age INT DEFAULT 0, 
    max_age INT
);
Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the divisions table:

INSERT INTO divisions(min_age, max_age)
VALUES(5,null),
        (20,null),
        (null,30);
Code language: SQL (Structured Query Language) (sql)

Third, query data from the divisions table:

SELECT
    id,
    min_age,
    max_age 
FROM 
    divisions;
Code language: SQL (Structured Query Language) (sql)

The following picture shows the output:

If a division does not require minimum age, the min_age column will have NULL. Similarly, if a division does not require maximum age, the max_age column will also have NULL.

Last, use the ISNULL() function to convert NULL in the min_age column to 0 and NULL in the max_age column to 99:

SELECT 
    id, 
    ISNULL(min_age,0) min_age, 
    ISNULL(max_age,99) max_age
FROM
    divisions;
Code language: SQL (Structured Query Language) (sql)

The following picture shows output:

In this tutorial, you have learned how to use the SQL Server ISNULL() function to replace NULL with a specified value.