Problem with very small decimals

Posted on 2009-06-29
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 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?
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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 500 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  Leave it M$ to muck about with your data! :-)

Search google for " displaying numbers in scientific notation" and there are a ton of results that should point you in the right direction.  I don't know 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: 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

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

732 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