How to convert scientific number to decimal?

Suppose you have number in scientific form and you want to migrate these number to a decimal field. How you approach to do this since cast function to decimal fails with scientific numbers

Now if have a number like this

'0.23e10'

Lets try to cast it to decimal(10,5)

select cast('0.23e10' as decimal(10,5))

Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to numeric.

So its fail. Ok lets try to cast it to float type.

select cast('0.00023e4' as float)

FloatCasted

----------------------

2.3

That’s great, now lets try to convert to decimal.

select cast(cast('0.00023e4' as float) as decimal(10,5)) decimalCasted

FloatCasted

---------------------------------------

2.30000

That’s workaround for scientific to decimal conversion.

Get your tea cup and start coding J

Post Reference: Vikram Aristocratic Elfin Share