In Oracle, the REMAINDER() function returns the remainder of its first argument divided by its second.

It’s similar to the MOD() function, except that it uses ROUND() in its calculation, whereas MOD() uses FLOOR() in its calculation.

Syntax

The syntax goes like this:

REMAINDER(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 REMAINDER(100, 6)
FROM DUAL;

Result:

   REMAINDER(100,6) 
___________________ 
                 -2 

REMAINDER() vs MOD()

The above result may appear to be unexpected, especially when compared to the MOD() function. But this is because MOD() uses the FLOOR() function in its formula, while REMAINDER() uses the ROUND() function.

Here are the two functions compared:

SELECT 
    REMAINDER(100, 6),
    MOD(100, 6)
FROM DUAL;

Result:

   REMAINDER(100,6)    MOD(100,6) 
___________________ _____________ 
                 -2             4

In this case, we get vastly different results from the two functions, even though they both return the remainder of its first argument divided by its second.

What’s going on?

Perhaps the easiest way to think about it is like this:

SELECT 
    100/6,
    ROUND(100/6) AS "ROUND()",
    FLOOR(100/6) AS "FLOOR()"
FROM DUAL;

Result:

     100/6    ROUND()    FLOOR()
---------- ---------- ----------
16.6666667         17         16

In this case we get a different result, depending on whether we use ROUND() or FLOOR().

• If we multiply 17 by 6, we get 102. This gives us a remainder of -2.
• If we multiply 16 by 6, we get 96. This gives us a remainder of 4.

If we change the 6 to a 7, both functions return the same result:

SELECT 
    REMAINDER(100, 7),
    MOD(100, 7)
FROM DUAL;

Result:

   REMAINDER(100,7)    MOD(100,7) 
___________________ _____________ 
                  2             2 

And here’s what ROUND() and FLOOR() return:

SELECT 
    100/7,
    ROUND(100/7) AS "ROUND()",
    FLOOR(100/7) AS "FLOOR()"
FROM DUAL;

Result:

     100/7    ROUND()    FLOOR()
---------- ---------- ----------
14.2857143         14         14

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 REMAINDER('Ponzi', 'Invest')
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT REMAINDER('Ponzi', 'Invest')
FROM DUAL
Error report -
ORA-01722: invalid number

Null Arguments

REMAINDER() returns null if any argument is null:

SET NULL 'null';

SELECT 
    REMAINDER(null, 2),
    REMAINDER(7, null),
    REMAINDER(null, null)
FROM DUAL;

Result:

   REMAINDER(NULL,2)    REMAINDER(7,NULL)    REMAINDER(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 REMAINDER() with the wrong number of arguments, or without any arguments results in an error:

SELECT REMAINDER()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT REMAINDER()
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 REMAINDER(10, 2, 3)
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT REMAINDER(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: