Using LEFT with LEN Turns Float Into Weird Format (e.g. 6.0871e)

I have about 70 million rows of data with a column that contains numbers, but it’s in a float format. I need to get rid of the last 4 digits of that column i.e. I need to turn this

60871003002001 60871003002002 60871003002003

into this

6087100300 6087100300 6087100300

When I run the query

select top 3 LEFT(COLUMN, LEN(COLUMN)-4) as a from TABLE

it returns the following:

6.0871e 6.0871e 6.0871e

Does anyone know why? I’m using SQL Server. There are no nulls and each number is from 12 to 15 digits long.

Thank you!

Answer

Instead, divide by 1000 and turn into a decimal:

select cast( (col / 10000) as decimal(18, 0))

The problem you are facing is that the default conversion of a float to a string might sometimes be in scientific notation.