varchar column returning exponential representation of number

Question

I have a varchar(max) column that sometimes returns me the exponential format of the number like so:

1e+006

How do I alter the column in the table to always have it show me the actual number and not an exponential representation of the number?

I would alter the column to a different data type or something, but I have no idea what I'd alter it to, since I don't understand why this is happening in a varchar column.

Answer 1

A varchar like '1e+006' can be converted to float, which can then be further converted to any numeric type you like.

So this might be as simple as

alter table mytable alter column mycolumn float

perhaps followed by a conversion to a decimal type:

alter table mytable alter column mycolumn decimal(20,5)