Converting floats stored in scientific notation back to decimals

I'm working on the CCK D6 port and I need help coming up
with the SQL to convert floats stored in scientific notation
back to decimals.

Up to now, CCK has used a float field to store the 'decimal'
field type, which works fine if the numbers are not too
large, but ends up stored in scientific notation if they
are. This has been a known CCK problem for a long time but
fixing it before now would have involved re-writing some of
the most complex code in CCK, so it hasn't gotten done.

In D6 we now have a real decimal field to use, which is
great, but I need to write an D6 update that will take the
data sometimes stored in scientific notation and sometimes
as decimal values in those float fields, and migrate it to
decimal fields without losing any data, and I badly need
help finding the right SQL to do this update.

You can easily create a test bed by creating a table with a
float field and a decimal field. Then store some small and
some very large integers and decimals, in the float field
and try to copy those stored values from the float field
back to the decimal field without losing or corrupting any
data.

And of course we have to do it in a way that works in both
MYSQL and Postgres 

Thanks!

Karen

Quoting Karen Stevenson <karenelderweb.com>:

> I'm working on the CCK D6 port and I need help coming
up with the SQL 
> to convert floats stored in scientific notation back to
decimals.
>

Read the data from the DB then <?php $data =
(float)(0+$data); ?> 
should convert the string back to floating.

Earnie -- http://for-my-kids.com/
-- http://give-me-an-offer.
com/

On 11/4/07, Karen Stevenson <karenelderweb.com> wrote:
> I'm working on the CCK D6 port and I need help coming
up with the SQL to convert floats stored in scientific
notation back to decimals.
>
> Up to now, CCK has used a float field to store the
'decimal' field type, which works fine if the numbers are
not too large, but ends up stored in scientific notation if
they are. This has been a known CCK problem for a long time
but fixing it before now would have involved re-writing some
of the most complex code in CCK, so it hasn't gotten done.
>
> In D6 we now have a real decimal field to use, which is
great, but I need to write an D6 update that will take the
data sometimes stored in scientific notation and sometimes
as decimal values in those float fields, and migrate it to
decimal fields without losing any data, and I badly need
help finding the right SQL to do this update.
>
> You can easily create a test bed by creating a table
with a float field and a decimal field. Then store some
small and some very large integers and decimals, in the
float field and try to copy those stored values from the
float field back to the decimal field without losing or
corrupting any data.
>
> And of course we have to do it in a way that works in
both MYSQL and Postgres 
>
> Thanks!
>
> Karen
>


I am a bit puzzled about the possibility of a generic
solution
covering decimal columns of any length. A conversion with a
simple
UPDATE table SET decimal_col = float_col would work fine,
but only if
the (digits, decimals) of the decimal column are large
enough to hold
the number, otherwise the results would be wrong.

Are you going to use something big, such as decimal(64,32)?
Or are you
looking for a conversion which preserves the most
significant digits
up to some point? How many decimal digits do you have in
mind?

Perhaps I don't understand this correctly, but storing the
values as
decimals looks like trouble to me.