Convert a string to int using sql query

Question

How to convert a string to integer using SQL query on SQL Server 2005?

Answer 1

You could use CAST or CONVERT:

SELECT CAST(MyVarcharCol AS INT) FROM Table  SELECT CONVERT(INT, MyVarcharCol) FROM Table 

Answer 2

Also be aware that when converting from numeric string ie '56.72' to INT you may come up against a SQL error.

Conversion failed when converting the varchar value '56.72' to data type int. 

To get around this just do two converts as follows:

STRING -> NUMERIC -> INT

or

SELECT CAST(CAST (MyVarcharCol AS NUMERIC(19,4)) AS INT) 

When copying data from TableA to TableB, the conversion is implicit, so you dont need the second convert (if you are happy rounding down to nearest INT):

INSERT INTO TableB (MyIntCol) SELECT CAST(MyVarcharCol AS NUMERIC(19,4)) as [MyIntCol] FROM TableA