Solved

decimal values (oracle /sql)

Posted on 2004-09-01
7
2,496 Views
Last Modified: 2008-03-10
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
0
Comment
Question by:oraram9
7 Comments
 

Author Comment

by:oraram9
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

by:ikardien
ID: 11957638
number - trunc(number,0) should do it
0
 
LVL 4

Expert Comment

by:Harish_Rajani
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

by:Harish_Rajani
ID: 11972332
for ORACLE the solution would be

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


Regards
Harish
0
 
LVL 8

Accepted Solution

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Database tuning – How to start and what to tune. This question is frequently asked by many people, both online and offline. There is no hard and fast rule-of-thumb for performance tuning, however, before beginning the tuning process one should a…
Read about achieving the basic levels of HRIS security in the workplace.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now