tSQL - Conversion from varchar to numeric works for all but integer

Question

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

User · Answer

Try this  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

User · Answer

Actually whether there are digits or not is irrelevant  The    dot  is forbidden if you want to cast to int  Dot can t - logically - be part of Integer definition  so even   select cast   7 0  as int  select cast   7   as int    will fail but both are fine for floats

User · 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

User · Answer

Presumably  you want to convert values before the decimal place to an integer   If so  use case and check for the right format   SELECT  case when varcharcol not like       then cast varcharcol as int               else cast left varcharcol  chardindex      varcharcol  - 1  as int          end  IntVal FROM MyTable

User · Answer

SELECT   convert numeric 18 5  Col1   Col2      FROM DBname dbo TableName          WHERE isnumeric isnull Col1 1    lt  gt  0

User · Answer

Try this query  SELECT cast column name as type  as col identifier FROM tableName WHERE 1 1  Before comparing  the cast function will convert varchar type value to integer type

[sql] tSQL - Conversion from varchar to numeric works for all but integer

Examples related to sql

Examples related to sql-server

Examples related to number-formatting