## Forum Stats

• 3,837,767 Users
• 2,262,296 Discussions

Discussions

# How can I get decimal part of a number?

Member Posts: 28
edited Nov 8, 2013 7:25AM
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

• Member Posts: 4,542
Hello,

Is this works for you,
``` SELECT   TO_NUMBER (SUBSTR ('33.455', INSTR ('33.455',
'.',
1,
1)
+ 1)) mydecimal
FROM   DUAL;```
Regards
«1

• 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,
• Member Posts: 28
But I shoul get realy an integer 456 (that is the decimal part)
• 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,
• Member Posts: 4,542
Hello,

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

But Can i use this inside a funcion PL/SQL?
• 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,
• 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
• 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

'^[0-9]+\.',
''));
END fnc_get_decimal_part;```
• 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.