TSQL - Convert from varchar to numeric works for all but integer

Question

I have a table with numbers in the varchar(255) field varchar(255) . They are more than one and have several decimal places. I would like to convert them to integers. According to every website I have consulted, including https://stackoverflow.com/a/312960/ , any of these should work:

 SELECT CAST(VarcharCol AS INT) FROM MyTable SELECT CONVERT(INT, VarcharCol) FROM MyTable 

They 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 worked on the problem by going over to the Decimal(6,0) data type, which works fine. But just for my training, can someone tell me why converting to an int (or integer ) data type gives me an error? Thanks.

Answer 1

Converting a VARCHAR value to INT fails when there are digits to the right of the decimal to prevent data loss.

If you convert to FLOAT first and then convert to INT, the conversion is performed.

 SELECT CAST(CAST('7082.7758172' as float) as int) 

returns

 7082 

Answer 2

Actually, whether there are numbers or not, it does not matter .. (period) is prohibited if you want to pass to int. A point cannot - logically - be part of an Integer definition, so even:

 select cast ('7.0' as int) select cast ('7.' as int) 

will fail, but both are great for floats.

Answer 3

try it

 declare @v varchar(20) set @v = 'Number' select case when isnumeric(@v) = 1 then @v else @v end 

and

 declare @v varchar(20) set @v = '7082.7758172' select case when isnumeric(@v) = 1 then @v else convert(numeric(18,0),@v) end 

Answer 4

Presumably you want to convert the values ​​before the decimal to an integer. If so, use case and verify the format is correct:

 SELECT (case when varcharcol not like '%.%' then cast(varcharcol as int) else cast(left(varcharcol, chardindex('.', varcharcol) - 1) as int) end) IntVal FROM MyTable;