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:

`MOD(m, n)`

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:

`MOD`

function 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 `m`

and `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:

*For*`n`

≠ 0:`SIGN(m) * (ABS(m) - FLOOR(ABS(m / n)) * ABS(n))`

*For*:`n`

= 0`m`

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 `m`

.

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.