Solved

# decimal values (oracle /sql)

Posted on 2004-09-01
2,528 Views
I have to retrive the values after the decimal point.the problem is the number of digits after the decimal point are not fixed.

for example

if Number is                        should get
--------------                       --------------

12.5463111111111           5463111111111
5.245377                         245377
7                                     0
11.09                               09
45                                    0

Ruth
0
Question by:oraram9
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

Author Comment

ID: 11956577
an addition to the above question..
it is okay if I can get the 5 digits after the decimal value

like

if Number is                        should get
--------------                       --------------

12.5463111111111           .54631
5.245377                         .24537
7                                     .0
11.09                               .09
45                                    .0

Thanks
0

Expert Comment

ID: 11957638
number - trunc(number,0) should do it
0

LVL 4

Expert Comment

ID: 11963668
Here is the solution:

select substring(cast(salary as varchar(30)),charindex('.', cast(salary as varchar(30))) , 6) from Emp

Emp is the name of table and salary is the numeric field in Emp table.

This will give u the 5 digits after decimal point.

Here I have assumed that the maximum width of "salary" field is 30.

0

LVL 4

Expert Comment

ID: 11972332
for ORACLE the solution would be

select substr(salary, instr(salary, '.'), 5) from Emp

Regards
Harish
0

LVL 8

Accepted Solution

Pierrick LOUBIER earned 50 total points
ID: 11982079
ORACLE only
You don't have to convert number to string.

SELECT <number_field> - FLOOR(<number_field>)
FROM <table>;

This will return the decimal part, as FLOOR returns the nearest integer smaller or equal.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question