SQL & PL/SQL

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> 

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>

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> 

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

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;

  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;

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;

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,

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

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