Introduction SQL

Basic SQL

SQL Constraints

Advance SQL

SQL Joins

SQL Function


SQL MOD, REMAINDER Function


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