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

x
Solved

# Problem with very small decimals

Posted on 2009-06-29
Medium Priority
788 Views
Hi

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

LVL 3

Expert Comment

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

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
0.0000872852595488195

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.
0

Author Comment

ID: 24744557
Hi

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?
0

LVL 29

Expert Comment

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

Why?
- 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...

0

LVL 3

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

0

LVL 60

Expert Comment

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
0

LVL 60

Expert Comment

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
``````
0

Author Closing Comment

ID: 31597848
Many thanks
0

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
0

LVL 60

Expert Comment

ID: 24748670

Best regards,
Kevin
0

## Featured Post

Question has a verified solution.

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