What is the difference between MOD and REMAINDER in oracle? What is the difference between MOD and REMAINDER in oracle? sql sql

# What is the difference between MOD and REMAINDER in oracle?

The documentation is pretty clear on the difference:

NOTE

The REMAINDER function uses the round function in its formula, whereas the MOD function uses the floor function in its formula.

In other words, when the arguments are positive integers, the mod function returns a positive number between 0 and the second argument. The remainder function returns a number whose absolute value is less than the second argument divided by 2.

The differences can be more striking for negative numbers. One example of a difference is:

``REMAINDER(-15, 4)MOD(-15, 4)``

The first gives `-3` and the second `1`.

EDIT:

What is happening here? How many times does 4 go into -15. One method is "-4" times with a remained of 1. That is: -15 = 4*(-4) + 1. The other is "-3" times: -15 = 4*(-3) - 3.

The difference what is -15/4 expressed as an integer. Using floor, you get `-4`. Using round, you get `-3`.

The difference between MOD and REMAINDER function can be understood in the example below:

MOD(13,5): returns 3 whereas, REMAINDER (13,5) returns -2

A simple way to understand the difference is that MOD uses the floor function therefore it counts the occurrence of the second number within the first and returns what is left to complete the first number i.e. 2(5) gives 10 adding 3 gives 13 therefore MOD(13,5)=3

However, the REMAINDER uses a Round function it therefore gets the total number of the second number that could make up the first and then subtract the what makes it excess. i.e. 3(5) = 15 and subtracting 2 gives 13,therefore REMAINDER(13,5)= -2

REMAINDER (-15, 4): 4(-4) gives -16 and adding +1 gives -15 hence REMAINDER (-15,4)=+1

MOD (-15, 4): 3(-4) gives -12, adding -3 gives us -15 hence MOD(15,4)=-3

REMAINDER(-15, 4)--uses round without taking the sign of the number into consideration.

hence -15/4= -3.75==> round(-3.75)= -4.--(ignore sign during round)

``         -4*4= -16         -15-(-16)=>1``

There fore: REMAINDER(-15, 4)=1

MOD(-15, 4)----uses Floor without taking the sign of the number into consideration.

``         -15/4= -3.75==> floor(-3.75)= -3.--(ignore sign in floor)          -3*4=-12          -15-(-12)=>-3``

There fore: MOD(-15, 4)= -3