SQL MOD, REMAINDER Functions

• « SQL Functions

SQL MOD Function

SQL MOD function return the reminder value of n1 divide by n2. Where n1 and n2 is natural value.

MOD function explicitly uses FLOOR function to finding modulus formula.

Supported Oracle SQL Version

• Oracle 8i
• Oracle 9i
• Oracle 10g
• Oracle 11g
• Oracle 12c

Syntax

MOD( n1, n2 )

Example :

Consider following MOD(24, 7) function return 24 divide by 7.

SQL> SELECT MOD(24,7) "MOD" FROM DUAL;

       MOD
----------
         3

SQL> SELECT MOD(20, 3) "MOD" FROM DUAL;

       MOD
----------
         2

SQL REMAINDER Function

SQL REMAINDER function return the remainder value of n1 divide by n2. Where n1 and n2 is natural value.

REMAINDER function explicitly uses ROUND function to finding remainder formula.

  r = remainder
        n = number
        d = divisor

        r = n - (d * (n / d)) 
        WHERE  (n / d) = (37 / 5) = 7
               (d * 7) = 5 * 7 = 35
               n - (35) = 37 - 35 = 2

Supported Oracle SQL Version

• Oracle 10g
• Oracle 11g
• Oracle 12c

Syntax

REMAINDER(n1,n2)

Example

Considering following example 4 raised to a 2 return the 16.

SQL> SELECT REMAINDER(37,5) "REMAINDER" FROM DUAL;

 REMAINDER
----------
         2

SQL> SELECT REMAINDER(29,5) "REMAINDER" FROM DUAL;

 REMAINDER
----------
        -1

SQL> SELECT REMAINDER(28,5) "REMAINDER" FROM DUAL;

 REMAINDER
----------
        -2

SQL> SELECT REMAINDER(27,5) "REMAINDER" FROM DUAL;

 REMAINDER
----------
         2

• « SQL Functions