There’s a lot of confusion over how Oracle calculates the return value for its
MOD function, and most people are surprised at the result when the dividend and/or the divisor are negative.
The Oracle function is called like this, where
m is the dividend and
n is the divisor:
The Oracle documentation explains that
MOD returns a remainder rather than a modulus, and if the divisor
n is zero it just returns the dividend
m. It then goes on to explain how the modulus isn’t calculated:
MODfunction with this formula:
m - n * FLOOR(m/n)
The same formula is repeated on techonthenet, but it’s described as how the Oracle modulus is calculated:
m - n * floor(m/n)
The formula above matches the result from Oracle’s
MOD function if
n are both positive, but if either or both are negative the results nearly always differ.
After some experimentation, I came up with the correct formula:
SIGN(m) * (ABS(m) - FLOOR(ABS(m / n)) * ABS(n))
As a one-liner, the formula would be one of the following:
COALESCE(SIGN(m) * (ABS(m) - FLOOR(ABS(m / NULLIF(n, 0))) * ABS(n)), m), or
DECODE(m, 0, m, SIGN(m) * (ABS(m) - FLOOR(ABS(m / n)) * ABS(n)))
I prefer the first one because I haven’t used
DECODE in years. I don’t think it’s superior to the second; it’s just the one I’m more comfortable with. The
NULLIF(n, 0) will return null if
n is zero, which turns the entire inner calculation to null. When that happens, the
COALESCE will change the null result to the value of
I verified the formula by writing a program to calculate about 10,000,000 modulus values, comparing its results to Oracle’s
MOD result. The operands for my formula were a mixture of positive/negative and integer/decimal for both the dividend and the divisor. Every one of the 10M calculations using the formula matched the Oracle modulus.
The same 10M calculations using the
m - n * FLOOR(m/n) formula differed nearly 50% of the time.
For the curious, the program used to test both formulas is here.