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.
This also works on a 10.2.0.4
Thanks for sharing!
Comment by Roeland — September 29, 2010 @ 2:42 pm UTC Sep 29,2010 |
For what it is worth, works in 11.2.0.1 as well.
Comment by Tony — September 29, 2010 @ 3:00 pm UTC Sep 29,2010 |
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 UTC Sep 29,2010 |
Neil,
That is only equivalent for the decimal portion less than .5:
Comment by Stephan Uzzell — September 29, 2010 @ 4:31 pm UTC Sep 29,2010 |
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 UTC Sep 29,2010 |
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 UTC Sep 30,2010 |
This works back to 9.2.0.8 as well
Comment by John — October 4, 2010 @ 6:49 pm UTC Oct 4,2010 |