I have a table with numbers in a varchar(255)
field. They’re all greater than one and have multiple decimal places. I’d like to convert them to integers. According to every web site I’ve consulted, including this one on StackOverflow, either of these should work:
SELECT CAST(VarcharCol AS INT) FROM MyTable SELECT CONVERT(INT, VarcharCol) FROM MyTable
These both work for me for every kind of numeric value but integer
– I can convert to float
, decimal
, etc. just fine, but trying to convert to integer
gives me the following error:
Conversion failed when converting the varchar value '7082.7758172' to data type int.
I’ve worked around the problem by converting to data type Decimal(6,0)
, which works fine. But just for my education, can anyone tell me why converting to data type int
(or integer
) gives me an error? Thanks.
Advertisement
Answer
Converting a varchar
value into an int
fails when the value includes a decimal point to prevent loss of data.
If you convert to a decimal
or float
value first, then convert to int
, the conversion works.
Either example below will return 7082:
SELECT CONVERT(int, CONVERT(decimal(12,7), '7082.7758172')); SELECT CAST(CAST('7082.7758172' as float) as int);
Be aware that converting to a float
value may result, in rare circumstances, in a loss of precision. I would tend towards using a decimal
value, however you’ll need to specify precision and scale values that make sense for the varchar
data you’re converting.