Oracle REMAINDER Function: A Simple Guide

The REMAINDER function in Oracle SQL, like the MOD function, calculates the remainder of a division. However, it uses a different internal formula, which can lead to different results, especially with negative numbers or fractions.

While MOD uses the FLOOR function (rounding down), REMAINDER uses the ROUND function (rounding to the nearest integer).

What is the REMAINDER Function in Oracle?

The REMAINDER(n2, n1) function calculates its result using the formula n2 - (n1 * N), where N is the integer nearest to n2 / n1.

This is the key difference from MOD:

• MOD(11, 4): 11 / 4 = 2.75. FLOOR(2.75) = 2. The result is 11 - (4 * 2) = 3.
• REMAINDER(11, 4): 11 / 4 = 2.75. ROUND(2.75) = 3. The result is 11 - (4 * 3) = -1.

Special rounding rule: If the result of n2 / n1 is exactly halfway (like x.5), the REMAINDER function rounds N to the nearest even integer.

REMAINDER Function Syntax

The syntax for REMAINDER requires two arguments:

REMAINDER(n2, n1)

Let's break that down:

• n2 (the dividend): The number to be divided.
• n1 (the divisor): The number to divide by.

Oracle REMAINDER Function Examples

Here are two practical examples that show how REMAINDER works.

Example 1: Basic Remainder Calculation

This example compares the result of REMAINDER and MOD for the same simple division.

Query:

SELECT 
  REMAINDER(11, 4) AS "Remainder_Result",
  MOD(11, 4) AS "Mod_Result"
FROM DUAL;

Result:

Remainder_Result Mod_Result
---------------- ----------
              -1          3

This result clearly shows the different calculation methods. REMAINDER rounded 2.75 up to 3, while MOD rounded it down to 2.

Example 2: The "Round to Even" Rule with REMAINDER

This example shows the special case where the division results in x.5. REMAINDER will round to the nearest even integer.

21 / 6 = 3.5. The nearest even integer is 4. 15 / 6 = 2.5. The nearest even integer is 2.

Query:

SELECT 
  REMAINDER(21, 6) AS "Rounds_to_4",
  REMAINDER(15, 6) AS "Rounds_to_2"
FROM DUAL;

Result:

Rounds_to_4 Rounds_to_2
----------- -----------
         -3           3

Calculation for REMAINDER(21, 6): 21 - (6 * 4) = 21 - 24 = -3 Calculation for REMAINDER(15, 6): 15 - (6 * 2) = 15 - 12 = 3