locked
Convert from scientific notation RRS feed

  • Question

  • I've got some values stored in nvachar(255) field stored by mistake as scientific notation (eg 7.5013e+006 instead of 7501301) and I need to convert and update the field with normal entry, not scientific notation. Is there a way to do that?

    Tuesday, January 22, 2008 3:25 AM

Answers

  • You have to use the following expression.

    select  replace(cast(cast(cast('7.5013e+006' as real) as money) as nvarchar(255)),'.00','')

    Why your nvarchar field got stored with scientific notation?

    When you convert real/float values to varchar/nvarchar, it will convert as the regualr decimal number when the digits are less than 7 otherwise it will use the scientific notation.

    So, here we are first parsing the string value into real and then parsing into money (money never uses the scientific notation). From money againg we are converting into varchar.

     

    Tuesday, January 22, 2008 4:52 AM
  • Thank you Manivannan for explaining this.

     

    I guess the following will work too..

    Code Block

    select str(cast('7.5013e+006' as real) )

     

     

    Tuesday, January 22, 2008 5:46 AM

All replies

  • You have to use the following expression.

    select  replace(cast(cast(cast('7.5013e+006' as real) as money) as nvarchar(255)),'.00','')

    Why your nvarchar field got stored with scientific notation?

    When you convert real/float values to varchar/nvarchar, it will convert as the regualr decimal number when the digits are less than 7 otherwise it will use the scientific notation.

    So, here we are first parsing the string value into real and then parsing into money (money never uses the scientific notation). From money againg we are converting into varchar.

     

    Tuesday, January 22, 2008 4:52 AM
  • Thank you Manivannan for explaining this.

     

    I guess the following will work too..

    Code Block

    select str(cast('7.5013e+006' as real) )

     

     

    Tuesday, January 22, 2008 5:46 AM
  • Obviously, with small correction..

    select LTRIM(str(cast('7.5013e+006' as real)))

    Tuesday, January 22, 2008 6:08 AM
  • Thanks very much for the help!!

    Wednesday, January 23, 2008 1:45 AM
  • I am concerned that nobody has pointed this out, so want to simply for clarification to others.

    The solutions provided by people will work, but are NEVER going to be 100% accurate.

    Every solution shown will convert 7.5013e+006 to 7501300.  It will NEVER resort back to the original 7501301

    Scientific Notation is used to give an approximation usable for large scale math, but throws away "insignificant" digits.  If the data you stored is a serial number, contract number or any other identifier, there ARE NO "insignificant" digits.

    In those cases, unless you can be 100% sure that you have a proper match, you should consider it lost data.

    Wednesday, July 10, 2019 12:15 PM