Oracle MOD() Function

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

Oracle MOD() syntax

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

MOD(y, x)

Parameters

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 MOD(y, x) function returns the remainder of y divided by x, which is the remainder of y/x.

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

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

Oracle MOD() Examples

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

Basic Usage

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

Output:

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

Negative numbers

When the product of two parameters is a negative number, the algorithm for calculating the modulus in Oracle is different from the classical algorithm. The following example illustrates all this:

SELECT
    11 "y",
    4 "x",
    MOD(11, 4) "MOD(y,x)",
    11 - 4 * FLOOR(11/4) "Classical"
FROM DUAL
UNION ALL
SELECT
    11,
    -4,
    MOD(11, -4),
    11 - -4 * FLOOR(11/-4)
FROM DUAL
UNION ALL
SELECT
    -11,
    4,
    MOD(-11, 4),
    -11 - 4 * FLOOR(-11/4)
FROM DUAL
UNION ALL
SELECT
    -11,
    -4,
    MOD(-11, -4),
    -11 - -4 * FLOOR(-11/-4)
FROM DUAL;

Output:

     y     x    MOD(y,x)    Classical
______ _____ ___________ ____________
    11     4           3            3
    11    -4           3           -1
   -11     4          -3            1
   -11    -4          -3           -3

NULL Parameters

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

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

Output:

   MOD(1,NULL)    MOD(NULL,1)    MOD(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 MOD() is a built-in function that returns the remainder after dividing two specified numbers.