Home » SQL & PL/SQL » SQL & PL/SQL » comparing decimal numbers
comparing decimal numbers [message #150487] Thu, 08 December 2005 02:29 Go to next message
murad_tamimi
Messages: 12
Registered: December 2005
Junior Member
Hi,

I need to compare decimal numbers in oracle. I know that comparing by equal sign does not yield expected results.

How do i go about doing this?

What if i wanted to compare a number that is just close to another number? (not exactly equal, but just close, maybe the difference could be the 6th decimal digit eg 45.6678547 and 45.6678549 - i would like the comparison to say that these two numbers are equal.

Is there a way to do that?

Thanks.

Murad
Re: comparing decimal numbers [message #150497 is a reply to message #150487] Thu, 08 December 2005 03:31 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Hope this will help you.
declare
  i number:=45.0;
  j number:=45.0000001;
begin
  if i-trunc(i) = 0 then  -- take care if anyone is integer
    i := i + 0.0000001;
  end if;
  if j-trunc(j) = 0 then
    j := j + 0.0000001;
  end if;
  if trunc(i) = trunc(j) and substr(rpad(i-trunc(i),7,'0'),2,7) = 
                        substr(rpad(j-trunc(j),7,'0'),2,7) then
    dbms_output.put_line('equal');
  else
    dbms_output.put_line('not equal');
  end if;
end;
/


I thought I could do that using abs but it didn't work.

By
Vamsi
Re: comparing decimal numbers [message #150521 is a reply to message #150487] Thu, 08 December 2005 05:12 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Have you tried ROUND()? Look it up in the manuals...

MHE
Re: comparing decimal numbers [message #150527 is a reply to message #150521] Thu, 08 December 2005 05:34 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Maaher,
But ROUND is not giving perfect results. Am I right?
For example, 45.6678547, 45.6678537 should not be equal. But ROUND gives it as equal.
If I'm wrong clarify me.

I think TRUNC is giving. Oops! I couldn't get it before.
declare
  i number :=45.66;
  j number:=45.6600009;
begin
  if trunc(i,6) = trunc(j,6) then
    dbms_output.put_line('equal');
  else
    dbms_output.put_line('not');
  end if;
end ;
/
Re: comparing decimal numbers [message #150551 is a reply to message #150527] Thu, 08 December 2005 06:48 Go to previous messageGo to next message
murad_tamimi
Messages: 12
Registered: December 2005
Junior Member
Hi,

i tried with round() and trunc...trunc does the job...thanks guys..

Thanks for the exmple Vamsi.

Murad
Re: comparing decimal numbers [message #150587 is a reply to message #150487] Thu, 08 December 2005 10:19 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Both round and trunc let you specify a position. I view the difference as a business answer goal sort of thing; depends on what you want the result to be in different situations:

MYDBA > select round(45.6673419,6), trunc(45.6673419,6) from dual;

ROUND(45.6673419,6) TRUNC(45.6673419,6)
------------------- -------------------
          45.667342           45.667341


Previous Topic: Urgent Data set.
Next Topic: insert problems
Goto Forum:
  


Current Time: Sun Jun 29 18:01:54 CDT 2025