16 June 2010

SQL divide by zero "isZero" function

Recently come across a requirement on SQL to divide 2 columns and populate in a custom fields. Infact is just something simple like "SELECT column1/column2 AS result"


Issue raised because the "column2" might contains zero or null value, what we need to do is if column 2 is zero replace it with another fix non zero varible.
  • Null issue can be easily solve by using isNull finction, but we don't have 'isZero' function
The solution is to use the nullif function with isnull function;
set column2 to null value if it is zero, outer level if is null replace with variable X.
ISNULL(NULLIF(column2, 0),variableX)

No comments:

Post a Comment