Using NULLIF() to avoid division by zero

When you perform divisions, you need to make sure that the divisor, the number to the right of the division operator, isn’t zero. If it is, you will end up with a division-by-zero error message:

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

Dividing by NULL, however, doesn’t. So to avoid division-by-zero errors, simply use NULLIF() to replace zeros in the divisor with a NULL value.

How the NULLIF() function works

The NULLIF() function is pretty much the opposite of the ISNULL() function. ISNULL(a, b) returns a, except if a is NULL. If a is NULL, it returns b.

NULLIF(a, b), however, returns NULL if a is equal to b.


Here’s the basic set-up, a temp table with some random values:

    a    numeric(10, 2) NOT NULL,
    b    numeric(10, 2) NOT NULL

INSERT INTO #test (a, b)
VALUES (7, 2), (3, 0), (5, 7), (9, 2);

The following query will return an error:

SELECT a, b, a/b
FROM #test;

But if you use NULLIF() in the divisor, the result of the division will instead be NULL, which is pretty close to the “undefined” result of a zero division.

SELECT a, b, a/NULLIF(b, 0.0)
FROM #test;

One thought on “Using NULLIF() to avoid division by zero

  1. Pingback: User options and connection flags « Sunday morning T-SQL

Let me hear your thoughts!

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.