Let Us Learn Oracle - Part 47 of N [ Remainder Function of Oracle 10g ]

Syntax: Remainder(x,y)

Purpose: It returns the remainder of x divided by y. It is similar to Mode function but the difference is that it uses Round function while Mod uses Floor function in it's formula while computing.

SQL> Select Remainder(10.75,6.99)RemainderExample, Mod(10.75,6.99) ModExample
  2  From Dual;

REMAINDEREXAMPLE MODEXAMPLE
---------------- ----------
           -3.23       3.76

Now let us see how the value of remainder function appears. It is calculated on the basis of the below formula

 x-(y * Round(x/y))

So we have

x = > 10.75

y => 6.99

Round (x/y) => 2

Now if we apply the values into the above formula and run the below query, we will get the same answer.

SQL> Select 10.75 - (6.99 * Round(10.75/6.99)) RemainderExample
  2  From Dual;

REMAINDEREXAMPLE
----------------
           -3.23

As said, Mod function uses Floor internally and henceforth the formula will be

 x-(y * Floor(x/y))

So we have

x = > 10.75

y => 6.99

Floor (x/y) => 1

Now if we apply the values into the above formula and run the below query, we will get the answer.

SQL> Select 10.75 - (6.99 * Floor(10.75/6.99)) ModExample
  2  From Dual;

MODEXAMPLE
----------
      3.76

So we understand the difference between Remainder and Mod function. I hope it is simple to make such a function in Sql Server as Floor and Round functions are both available in SQL Server

Thanks for reading