Division by zero is something that, sooner or later, we all have to capture. For the rest of this post, I am using a small temporary table that I have imaginatively named DIV0. It has two columns, COLUMN1 and COLUMN2 and it looks like this:
COLUMN1 COLUMN2 5 0 6 2
Until recently, I have tended to use a
CASE statement to check the column values before attempting to divide. It looks something like this:
select COLUMN1, COLUMN2, case when COLUMN2 = 0 then null else COLUMN1/COLUMN2 end as DIVIDED from div0
… and the output will look something like this:
COLUMN1 COLUMN2 DIVIDED 5 0 - 6 2 3.00000000000000000000000000
This is fine in as far as it goes, but when COLUMN2 is replaced with a calculation, it can all get very messy very quickly.
It turns out that the NULLIF function provides a better approach.
The NULLIF function returns a null value if the arguments compare equal, otherwise it returns the value of the first argument.
What this means is that I can simplify my earlier query to look like this:
select COLUMN1, COLUMN2, COLUMN1 / NULLIF(COLUMN2, 0) as DIVIDED from div0
And the output remains the same.