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


Problem with very small decimals

Posted on 2009-06-29
Medium Priority
Last Modified: 2012-08-13

I am just a novice mysql user so hopefully you will be able to help me.

I am storing some very small numbers in a field of type double. Some of the numbers are being stored as values like this: 8.72852595488195e-005. This is no good to me because some of the 3rd party vb.net components that I work with cannot handle the data in this form (ie. e-005).

Can i prevent the database from storing the values with this notation? Perhaps I need to use a different data type?

many thanks
Question by:andieje
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
  • 3
  • 3
  • 2
  • +1

Expert Comment

ID: 24735380
I have never seen mysql store values like that.  This sounds like a problem with your application.
LVL 29

Expert Comment

ID: 24743298
I think you need to make a distinction between:
- how the number is stored, and more specifically how many significant digits are kept, or more precisely are usable
- how the number is displayed.
(and remember than values are stored with binary digits, while they are rendered as decimal digits... which adds some problems).

When looking at the value, it is describing

If you know that all your values will be in the range of 0.5e-5 (ie 0.000 005) to 10.0e-5 (ie 0.000 100), and that you will need 3 significant digits eg 0.598e-5 (0.000 00598) you might consider storing not the values but the values time e+8, ie scaling them to 100 000 000 times the value.
0.598e-5 would be stored as 598,  10.0e-5 as 10 000
No problem with additions and substarction, you would just need to remember that these values are to be divided by e+8, ie by 100 000 000.
BEWARE though that you have to take a special care
- if you are multiplying or dividing by a "normal" value (eg 13%, or one-tenth, or 10 times), no problem (but remember that you will have to round the result to integer value)
- if you are multiplying two of the numbers together.... remember that you will have to divide the "integer result" by the scaling factor 100 000 000 (once)
- if you are dividing two of the numbers together... nothing to do, scale is correct.

Author Comment

ID: 24744557

The values can be anything from large to very small.

The data in this form -  8.72852595488195e-005- must come from an application I am using which is storing the data direclty in the database. Is there anyway I can get mysql to convert this data to its other form (0.0000872852595488195) when data like this is written to the database? Perhaps you could use an insert/update trigger?
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

LVL 29

Expert Comment

ID: 24744870
Yes you can, but in fact you can't.

- you define (ie it cannot be changed) the width of the field
- you can define that you want 20 digits out of which 19 after the decimal point... you can define 20 digits out of which 10 after the decimal point... but you cannot have BOTH at the same time

So if you want to cover a range of 10 digits before the decimal points (and say 8 significant digits) down to 12 after the decimal point (out of which still 8 significvant digits)... you will need to go into "scientific notation" which will use powers of ten as a scaling factor.

BEWARE of the significance of computations in these situations... specially when dealing with additions and substractions.
eg if A=3.E+10 (30 000 000 000), B=2.E-5 (0.00002), and if you add them keeping 8 significant digist, you will be in a "non-mathematical situation" where A+B and A are (numerically) equal while B>0...


Accepted Solution

psycle earned 2000 total points
ID: 24744885
The problem is not mysql.  If you login directly to mysql and enter large decimal numbers into the database and select them back out, you'll see that they are exactly as you entered them.  I believe the problem is vb.net.  Leave it M$ to muck about with your data! :-)

Search google for "vb.net displaying numbers in scientific notation" and there are a ton of results that should point you in the right direction.  I don't know vb.net well enough to give you code and I don't feel good about copying pasting an example from google that you could easily get yourself.

It seems that if you convert the number to a string it will display properly.  I said I wasn't going to do this, but here is an example showing the problem...

Dim d As Double = Math.PI / 10000000000
Dim ds As String
ds = d.ToString() 'with scientific notation
ds = d.ToString("N20") 'without

LVL 60

Expert Comment

by:Kevin Cross
ID: 24746797
It is how it is being presented to you most likely as you suspect.  The data should be stored without the scientific notation.  On the VB.NET side, use Decimal instead of Double.

Dim d As Decimal
LVL 60

Expert Comment

by:Kevin Cross
ID: 24746814

Dim d As Decimal = Math.PI / 10000000000
MsgBox(d) 'Without scientific notation
Dim ds As String = d.ToString() 
MsgBox(ds) 'Also without scientific notation

Open in new window


Author Closing Comment

ID: 31597848
Many thanks

Author Comment

ID: 24748229
mwvisa - I'm sorry i did not see your comment about using a decimal. That was actually key to fixing the issue. I had to this

dim d as decimal = getReallySmallValueasDouble.tostring("N20")

to get rid of the notation
LVL 60

Expert Comment

by:Kevin Cross
ID: 24748670
Just glad to help.

Best regards,

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
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