Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

decimal values (oracle /sql)

Posted on 2004-09-01
7
Medium Priority
?
2,540 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
[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
  • Learn & ask questions
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 200 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

618 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