BigQuery division by zero - How to do safe error handling

Problem 🔍

Handling run time SQL errors due to division by zero

SELECT  ColA/ColB;

If the denominator column (ColB) is 0, then it throw the error "division by zero"

Solution 💡

Method #1 Check the denominator is not zero during division using NULLIF

The syntax of NULLIF function:

NULLIF(expression1, expression2)

if expression1 = expression2 then it will return NULL, otherwise it will return expression1. Look at the below example

SELECT NULLIF(10, 10);
Returns NULL

You can now wrap the denominator in the division with the NULLIF function

SELECT ColA / ColB;

to

SELECT ColA / NULLIF(ColB, 0);

Method #2 Use SAFE_DIVIDE to handle run time errors

In standard SQL you can use SAFE_DIVIDE(x, y)
It is an equivalent to the division operator (/). Returns NULL if an error occurs, such as division by zero

SELECT SAFE_DIVIDE(ColA,ColB);

Best Practice ⚡

It is best practice to be proactive and use these mechanisms so that code does not fail in production environment

mCollins

posted on 14 Feb 19