Forum Stats

  • 3,837,767 Users
  • 2,262,296 Discussions
  • 7,900,386 Comments

Discussions

How can I get decimal part of a number?

690383
690383 Member Posts: 28
edited Nov 8, 2013 7:25AM in SQL & PL/SQL
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
BluShadow996803opiends2906185miersuzzemes

Best Answer

«1

Answers

  • Walter Fernández
    Walter Fernández Member Posts: 1,539 Bronze Trophy
    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
    690383 Member Posts: 28
    But I shoul get realy an integer 456 (that is the decimal part)
  • Walter Fernández
    Walter Fernández Member Posts: 1,539 Bronze Trophy
    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
    OrionNet Member Posts: 4,542
    Answer ✓
    Hello,

    Is this works for you,
     SELECT   TO_NUMBER (SUBSTR ('33.455', INSTR ('33.455',
                                                 '.',
                                                 1,
                                                 1)
                                          + 1)) mydecimal
      FROM   DUAL;
    Regards
  • 690383
    690383 Member Posts: 28
    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
    690383 Member Posts: 28
    THIS IS CORRECT!
    THANKS

    But Can i use this inside a funcion PL/SQL?
  • Walter Fernández
    Walter Fernández Member Posts: 1,539 Bronze Trophy
    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
    OrionNet Member Posts: 4,542
    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
    Walter Fernández Member Posts: 1,539 Bronze Trophy
    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
    OrionNet Member Posts: 4,542
    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;
This discussion has been closed.