In Oracle, the MOD() function returns the modulo operation. In other words, it returns the remainder of its first argument divided by its second.

Syntax

The syntax goes like this:

MOD(n2, n1)

Each argument can be any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type.

Example

Here’s an example:

SELECT MOD(100, 6)
FROM DUAL;

Result:

   MOD(100,6) 
_____________ 
            4

Oracle MOD() vs Classical Modulus

Oracle’s MOD() function behaves differently from the classical mathematical modulus function, if the product of n1 and n2 is negative.

Here’s an example that demonstrates how each of these differ:

SELECT 
    MOD(100, 6) AS "Oracle MOD()",
    100 - 6 * FLOOR(100/6) AS "Classical"
FROM DUAL

UNION ALL

SELECT 
    MOD(100, -6),
    100 - -6 * FLOOR(100/-6)
FROM DUAL

UNION ALL

SELECT 
    MOD(-100, 6),
    -100 - 6 * FLOOR(-100/6)
FROM DUAL

UNION ALL

SELECT 
    MOD(-100, -6),
    -100 - -6 * FLOOR(-100/-6)
FROM DUAL;

Result:

Oracle MOD()  Classical
____________  _________
           4	      4
           4	     -2
          -4	      2
          -4	     -4

Non-Numeric Arguments

The arguments can be any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type.

Here’s an example of what happens when the arguments don’t satisfy that criteria:

SELECT MOD('Homer', 'Symptom')
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT MOD('Homer', 'Symptom')
FROM DUAL
Error report -
ORA-01722: invalid number

Null Arguments

MOD() returns null if any argument is null:

SET NULL 'null';

SELECT 
    MOD(null, 16),
    MOD(1024, null),
    MOD(null, null)
FROM DUAL;

Result:

   MOD(NULL,16)    MOD(1024,NULL)    MOD(NULL,NULL) 
_______________ _________________ _________________ 
           null              null              null 

By default, SQLcl and SQL*Plus return a blank space whenever a null value occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Missing Arguments

Calling MOD() with the wrong number of arguments, or without any arguments results in an error:

SELECT MOD()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT MOD()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action:

And:

SELECT MOD(10, 2, 3)
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT MOD(10, 2, 3)
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action: