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