• Status: Solved
• Priority: Medium
• Security: Public
• Views: 2554

# decimal values (oracle /sql)

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
oraram9
1 Solution

Author Commented:
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

Commented:
number - trunc(number,0) should do it
0

Commented:
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

Commented:
for ORACLE the solution would be

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

Regards
Harish
0

IS Operational Excellence ManagerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.