Convert varchar to a number (scientific format)

• ikorenbl

  SSCommitted

  Points: 1750

  More actions

  February 24, 2005 at 10:34 am

  #60986

  Is there an easier way to convert varchar numbers in the scientific format to decimal format?

  For example,

  create table a (num varchar(100) null)

  insert into a

  select '1.4634128977659954E-2'

  Is there an easier way to do the following?

  select CASE WHEN CHARINDEX('E', num) > 0 THEN LEFT(num, CHARINDEX('E', num) - 1) * POWER(10.0000, SUBSTRING(num, CHARINDEX('E', num) + 1, 100)) ELSE num END

  from a

  Thanks

• ikorenbl

  SSCommitted

  Points: 1750

  More actions

  February 24, 2005 at 3:32 pm

  #544040

  I guess I'll answer my own question. All you need to do is cast it to float.

  create table a (num varchar(100) null)

  insert into a

  select '1.4634128977659954E-2'

  select cast(cast(num as float) as decimal(18,2))