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


Thanks in advance
Ruth
oraram9Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Pierrick LOUBIERConnect With a Mentor 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
 
oraram9Author 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
 
ikardienCommented:
number - trunc(number,0) should do it
0
 
Harish_RajaniCommented:
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
 
Harish_RajaniCommented:
for ORACLE the solution would be

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


Regards
Harish
0
All Courses

From novice to tech pro — start learning today.