Oracle Scratchpad

September 29, 2010

mod()

Filed under: Infrastructure,Oracle — Jonathan Lewis @ 2:11 pm BST Sep 29,2010

Here’s a little trick I’ve only just discovered (by accident). What’s a tidy way to extract the fractional part from a number ?
Running 11.1.0.6 (I haven’t checked earlier versions yet – or looked at the manuals for the definition of the mod() function):

SQL> select mod(1.34,1) from dual;

MOD(1.34,1)
-----------
        .34

1 row selected.

SQL> select mod(-1.34,1) from dual;

MOD(-1.34,1)
------------
        -.34

I really didn’t think it would make sense to use an expression like mod(p,1) – but it’s pleasant surprise that is does what it does.

7 Comments »

  1. This also works on a 10.2.0.4

    Thanks for sharing!

    Comment by Roeland — September 29, 2010 @ 2:42 pm BST Sep 29,2010 | Reply

  2. For what it is worth, works in 11.2.0.1 as well.

    Comment by Tony — September 29, 2010 @ 3:00 pm BST Sep 29,2010 | Reply

  3. i like this better….

    SQL> select remainder(1.34,1) from dual;

    REMAINDER(1.34,1)
    —————–
    .34

    Comment by Neil Barsema — September 29, 2010 @ 3:45 pm BST Sep 29,2010 | Reply

  4. Neil,

    That is only equivalent for the decimal portion less than .5:

    SQL> select base
      2       , mod( base, 1 )
      3       , remainder( base, 1 )
      4    from (
      5         select 1+level*.01 base
      6           from dual
      7        connect by level <=100
      8         )
      9  -- where mod( base, 1 )  remainder( base, 1 )
     10  /
    
       BASE MOD(BASE,1) REMAINDER(BASE,1)
    ------- ----------- -----------------
       1.01        0.01              0.01
       1.02        0.02              0.02
       1.03        0.03              0.03
       1.04        0.04              0.04
       1.05        0.05              0.05
       1.06        0.06              0.06
       1.07        0.07              0.07
       1.08        0.08              0.08
    
    
    
       1.49        0.49              0.49
       1.50        0.50             -0.50
       1.51        0.51             -0.49
       1.52        0.52             -0.48
       1.53        0.53             -0.47
       1.54        0.54             -0.46
       1.55        0.55             -0.45
    
    &c.
    

    Comment by Stephan Uzzell — September 29, 2010 @ 4:31 pm BST Sep 29,2010 | Reply

  5. I don’t think oracle is of any authority in math domain. Wolfram alpha returns
    Mod(-10,6)=2
    where oracle thinks it is -4. Likewise, for mod(-1.34,1) Wolfram return 0.66 (which makes sense since modulo N is expected in the range [0,N)

    Comment by Vadim Tropashko — September 29, 2010 @ 8:39 pm BST Sep 29,2010 | Reply

    • hi Mikito,

      so what ?

      concerning maths: it’s all a question of definition. you have to define your stuff. and you are free to define your stuff like you want.
      Oracle defines it

      from doc:


      This function behaves differently from the classical mathematical modulus function when m is negative. The classical modulus can be expressed using the MOD function with this formula:

      n2 – n1 * FLOOR(n2/n1)

      they call it mod. they also could call it add or trunc or cat or monkey or floor or remainder or whatever
      it has NOTHING whatsoever to do with “authority in math domain”

      Sokrates

      Comment by Sokrates — September 30, 2010 @ 7:13 am BST Sep 30,2010 | Reply

  6. This works back to 9.2.0.8 as well

    Comment by John — October 4, 2010 @ 6:49 pm BST Oct 4,2010 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.