How can I get decimal part of a number?

690383

Hello!
If a Have a decimal number, I want to get the decimal part.
Example ( 33.455 should return 455)
Thanks!

Edited by: fvilaca on Mar 11, 2009 6:32 PM

OrionNet

Hello,

Is this works for you,

 SELECT   TO_NUMBER (SUBSTR ('33.455', INSTR ('33.455',
                                             '.',
                                             1,
                                             1)
                                      + 1)) mydecimal
  FROM   DUAL;

Regards

Walter Fernández

Hi,

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0 
Connected as hr
 
SQL> 
SQL> with data as(
  2  select 12.456 as val from dual)
  3  select val - trunc(val) from data;
 
VAL-TRUNC(VAL)
--------------
         0,456
 
SQL> 

Regards,

690383

But I shoul get realy an integer 456 (that is the decimal part)

Walter Fernández

Hi,

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0 
Connected as hr
 
SQL> 
SQL> with data as(
  2  select 12.456 as val from dual)
  3  select to_number(regexp_replace(to_char(val), '^[0-9]+\.', '')) from data;
 
TO_NUMBER(REGEXP_REPLACE(TO_CH
------------------------------
                           456
 
SQL>

Regards,

OrionNet

Hello,

Is this works for you,

 SELECT   TO_NUMBER (SUBSTR ('33.455', INSTR ('33.455',
                                             '.',
                                             1,
                                             1)
                                      + 1)) mydecimal
  FROM   DUAL;

Regards

690383

Almost perfect!

If you change to other number like 3.56, I can´t get 56.
But it is possible using a funcion PL/SQL?

690383

THIS IS CORRECT!
THANKS

But Can i use this inside a funcion PL/SQL?

Walter Fernández

Hi,

I don't understand you (my solution and OrionNet's work fine):

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0 
Connected as hr
 
SQL> 
SQL> with data as(
  2  select 3.56 as val from dual)
  3  select to_number(regexp_replace(to_char(val), '^[0-9]+\.', '')) from data;
 
TO_NUMBER(REGEXP_REPLACE(TO_CH
------------------------------
                            56
 
SQL> 
SQL> SELECT   TO_NUMBER (SUBSTR ('3.56', INSTR ('3.56',
  2                                               '.',
  3                                               1,
  4                                               1)
  5                                        + 1)) mydecimal
  6    FROM   DUAL;
 
 MYDECIMAL
----------
        56
 
SQL> 

Regards,

OrionNet

Hello,

ANother way is this

SELECT   TO_NUMBER (SUBSTR ('33.455', INSTR ('33.455',
                                             '.',
                                             1,
                                             1)
                                      )) mydecimal
  FROM   DUAL;


 MYDECIMAL
----------
      .455

or

SELECT   TO_NUMBER (SUBSTR ('33.455', INSTR ('33.455','.',1,1)+1     )) mydecimal
  FROM   DUAL;

 MYDECIMAL
----------
       455

Regards

Walter Fernández

Hi,

SQL> select fnc_get_decimal_part(3.56) from dual;
 
FNC_GET_DECIMAL_PART(3.56)
--------------------------
                        56

CREATE OR REPLACE FUNCTION fnc_get_decimal_part(p_number IN NUMBER) RETURN NUMBER IS
BEGIN

   RETURN to_number(regexp_replace(to_char(p_number),
                                   '^[0-9]+\.',
                                   ''));
END fnc_get_decimal_part;

OrionNet

Hello

yes its possible to use in pl/sql, here is simple example of pl/sql block. Let me knwo if it works for you

  DECLARE
   v_number   NUMBER;
BEGIN
   SELECT   TO_NUMBER (SUBSTR ('33.455', INSTR ('33.455',
                                                '.',
                                                1,
                                                1)
                                         + 1))
               mydecimal
     INTO   v_number
     FROM   DUAL;

   DBMS_OUTPUT.put_line (v_number);
END;