OracleBrains.Com header image 2
← Coming Events::COLLABORATE 08 Dropping and Creating Database Enterprise Manager or Database Controller →

REMAINDER Function

December 14th, 2007 by Rajender Singh · 5 Comments

I don’t why but I never came across this function, may be because I am so used to using MOD function to get the remainder of two number when one of them is divided by others, so never though of finding any other function doing same thing.

But yesterday I came across this function while surfing on the net and thought may be many of us may be missing it too.

So sharing for those person who have missed it like me…..

REMAINDER(n2,n1)

REMAINDER returns the remainder of n2 divided by n1 . This function takes as arguments any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype. The MOD function is similar to REMAINDER except that it uses FLOOR in its formula, whereas REMAINDER uses ROUND.

Definition take from oradev.com


Tags: SQL and PL/SQL

5 responses so far ↓

  • 1 gary // Dec 17, 2007 at 6:06 am

    Just to add that it was a 10g addition.

  • 2 Rajender Singh // Dec 17, 2007 at 10:30 pm

    Thanks Gary!

    That information completes my post!

  • 3 Sumathi Muthukrishnan // Dec 18, 2007 at 12:10 pm

    As per your explanation regarding, REMAINDER and MOD functions. I ran some SQL Statements but I could not find out appropriate results.

    For e.g.

    1. SELECT MOD (9.569, 2) FROM DUAL

    Ans: 1.569

    2. SELECT REMAINDER (9.569, 2) FROM DUAL

    Ans: -0.431

    3. SELECT REMAINDER (9.569, 5) FROM DUAL

    Ans: -0.431

    I am unable to understand REMAINDER function results. Could you please explain the result? Thanks in advance.

  • 4 Rakesh Kumar Gupta // Dec 18, 2007 at 4:23 pm

    Hi Sumathi,

    REMAINDER Function works in this manner.
    For e.g. We want REMAINDER(m,n), so
    If n != 0, then the remainder is m - (n*N) where N is the integer nearest m/n.

    Like :

    REMAINDER(9.569, 2) => m=9.569, n=2

    m/n = 4.7845 , the nearest integer to 5, so:
    remainder = 9.569 - (2*5) = -0.431

    SELECT REMAINDER(9.569,2) FROM DUAL

    Ans: -0.431

    If m is a floating-point number, and if the remainder is 0 (zero), then the sign of the remainder is the sign of m. Remainders of 0 (zero) are unsigned for NUMBER values

    SELECT REMAINDER(4.56,2) FROM DUAL

    Ans: 0.56

    Now I think you can understand how REMAINDER Function is working.

    Regards,
    Rakesh

  • 5 Rajender Singh // Dec 18, 2007 at 11:18 pm

    Hi Sumathi,

    I hope you understood what Rakesh has explained

    If you need more help can check out following url:

    Remainder Function
    http://www.techonthenet.com/oracle/functions/remainder.php

    Mod Function
    http://www.techonthenet.com/oracle/functions/mod.php

    Regards

Leave a Comment