How to fix scientific notation (like 1.23E+004) number data in varchar column that are imported from Excel in SQL ServerPosted: January 4, 2017
When you import data from Excel, sometimes the numeric column (float type) can be implicit converted to scientific notation.
A number 142972 can be saved as 1.42972E+005 as a string, which may cause problem.
But you can convert the data back to its normal numeric format with some T-SQL functions.
Here is a sample code snippet:
create table test (col varchar(15)) insert into test values('1.42972E+007'),('12345678'),('ABCDE12345') select ISNULL(Str(Try_CONVERT(float, col)),col) as col from test
drop table test