Oracle REMAINDER() Function

Oracle REMAINDER() is a built-in function that returns the remainder after dividing two specified numbers.

The Oracle REMAINDER() function is similar MOD, except that it uses ROUND in its formula, but MOD uses FLOOR

Oracle REMAINDER() syntax

Here is the syntax for the Oracle REMAINDER() function:

REMAINDER(y, x)

Parameters

num

Required.

y

Required. dividend.

x

Required. divisor.

y and x can be any numeric data type or any non-numeric data type that can be implicitly converted to a numeric data type.

Return Value

The Oracle REMAINDER(y, x) function returns the remainder of y divided by x, which is the remainder of y/x.

If x is 0, the REMAINDER(y, x) function returns y.

If any parameter is NULL, REMAINDER() will return NULL.

Oracle REMAINDER() Examples

Here are some examples that demonstrate the usage of the Oracle REMAINDER() function.

Basic Usage

SELECT
    REMAINDER(3, 2),
    REMAINDER(3.3, 1.2)
FROM dual;

Output:

   REMAINDER(3,2)    REMAINDER(3.3,1.2)
___________ _______________
          1             0.9

REMAINDER() vs MOD

Unlike MOD, REMAINDER() uses ROUND in its formulas, and MOD uses FLOOR.

SELECT
    REMAINDER(14, 3),
    MOD(14, 3)
FROM dual;

Output:

   REMAINDER(14,3)    MOD(14,3)
__________________ ____________
                -1            2

The following statement explains it all:

SELECT
    14 - 3 * ROUND(14/3) "REMAINDER",
    14 - 3 * FLOOR(14/3) "MOD"
FROM dual;

Output:

   REMAINDER    MOD
____________ ______
          -1      2

Here, the calculation methods of REMAINDER() and MOD are as follows:

  • REMAINDER(y, x) uses y - x * ROUND(y/x)
  • MOD(y, x) uses y - x * FLOOR(y/x)

NULL Parameters

If any parameter is NULL, REMAINDER() will return NULL.

SET NULL 'NULL';
SELECT
    REMAINDER(1, NULL),
    REMAINDER(NULL, 1),
    REMAINDER(NULL, NULL)
FROM dual;

Output:

   REMAINDER(1,NULL)    REMAINDER(NULL,1)    REMAINDER(NULL,NULL)
____________________ ____________________ _______________________
                NULL                 NULL                    NULL

In this example, we use the statement SET NULL 'NULL'; to display NULL values as the string 'NULL'.

Conclusion

Oracle REMAINDER() is a built-in function that returns the remainder after dividing two specified numbers.