Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How can I get decimal part of a number?

690383Mar 11 2009 — edited Nov 8 2013
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
This post has been answered by OrionNet on Mar 11 2009
Jump to Answer

Comments

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
Answer
Hello,

Is this works for you,
 SELECT   TO_NUMBER (SUBSTR ('33.455', INSTR ('33.455',
                                             '.',
                                             1,
                                             1)
                                      + 1)) mydecimal
  FROM   DUAL;
Regards
Marked as Answer by 690383 · Sep 27 2020
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;
OrionNet
Hello

Here is using function
CREATE OR REPLACE FUNCTION rdecimal (i_nbr IN NUMBER)
   RETURN NUMBER
AS
   v_number   NUMBER;
   v_nbr      VARCHAR2 (40);
BEGIN
   v_nbr := TO_CHAR (i_nbr);

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

   -- DBMS_OUTPUT.put_line (v_number);
   RETURN v_number;
END;

select rdecimal(33.455) from dual;
Regards
MArine

Hi to all,

I think the best way (with better performance) is using mathematical function "mod", with the second parameter = "1". For example:

>select mod(3.1416, 1) as frac_part from dual;

FRAC_PART

----------

     ,1416

1 row selected.

Regards,

BluShadow

Do you think it's a good idea to drag up a thread that is nearly 5 years old?

You're right that MOD can be used to answer this question, but the OP is surely not sitting around still waiting for more answers, unlike the people who have asked questions recently that still need answering.

DO NOT drag up old threads unnecessarily.

Locking this thread

PhHein

@"user5154192"Reporting abuse on a reasonable moderator action is a bit silly, no?

1 - 14
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 6 2013
Added on Mar 11 2009
14 comments
31,127 views