OraFAQ Forum: SQL & PL/SQL » get the decimal value

Home » SQL & PL/SQL » SQL & PL/SQL » get the decimal value

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

get the decimal value [message #32385]

Mon, 16 August 2004 02:25

subhasish
Messages: 33
Registered: May 2000

Member

my requirement is as follow..

work hours in numbers are like

13.16
11.23
11.09
0
11.1
13.06
11.2
11
12.08
11.01
13

I want to get the decimal values
like
16
23
9
0
1
6
2
0
8
1
0

thanks in advance

Report message to a moderator

Re: get the decimal value [message #32388 is a reply to message #32385]

Mon, 16 August 2004 02:54

Venkat
Messages: 110
Registered: February 2001

Senior Member

the below query prints fractional part of a salary column of emp table

select decode(instr(sal,'.'),0,0,substr(sal,instr(sal,'.')+1)) from emp

Report message to a moderator

Re: get the decimal value [message #32390 is a reply to message #32388]

Mon, 16 August 2004 03:23

Ultra
Messages: 98
Registered: November 2003

Member

select
nvl(
rtrim(100000000000000000000000*(sal-round(sal)),0)
,0)
from emp

(doesn't work if the number of decimal
is to big, but it's not dependant of
the number format)

Report message to a moderator

Re: get the decimal value [message #32391 is a reply to message #32385]

Mon, 16 August 2004 03:33

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

How are you going to tell the difference between 11.2 and 11.02, since it appears from your examples that you would want both values to return 2?

Given your data, here are two columns; one of what I think your result should be, and one of what you asked for.

SQL> CREATE TABLE t (hours NUMBER);
 
Table created.
 
SQL> INSERT INTO t VALUES (13.16);
SQL> INSERT INTO t VALUES (11.23);
SQL> INSERT INTO t VALUES (11.09);
SQL> INSERT INTO t VALUES (0);
SQL> INSERT INTO t VALUES (11.1);
SQL> INSERT INTO t VALUES (13.06);
SQL> INSERT INTO t VALUES (11.2);
SQL> INSERT INTO t VALUES (11);
SQL> INSERT INTO t VALUES (12.08);
SQL> INSERT INTO t VALUES (11.01);
SQL> INSERT INTO t VALUES (13);
SQL> SELECT t.hours
  2  ,      MOD(t.hours * 100,100)                               should_be
  3  ,      DECODE(MOD(t.hours * 100,100)
  4         ,      0, 0
  5         ,      TO_NUMBER(RTRIM(TO_CHAR(MOD(t.hours * 100
  6                                        ,   100))
  7                          ,     '0')))                        you_asked_for
  8  FROM   t
  9  /
 
     HOURS  SHOULD_BE YOU_ASKED_FOR
---------- ---------- -------------
     13.16         16            16
     11.23         23            23
     11.09          9             9
         0          0             0
      11.1         10             1
     13.06          6             6
      11.2         20             2
        11          0             0
     12.08          8             8
     11.01          1             1
        13          0             0
 
11 rows selected.
 
SQL>

Let's be careful out there.

Art.

Report message to a moderator