# 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## Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds

Post Comment