Convert a string to int using sql query

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

Solution 1:

You could use CAST or CONVERT:

SELECT CAST(MyVarcharCol AS INT) FROM Table

SELECT CONVERT(INT, MyVarcharCol) FROM Table

Solution 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

Solution 3:

Starting with SQL Server 2012, you could use TRY_PARSE or TRY_CONVERT.

SELECT TRY_PARSE(MyVarcharCol as int)

SELECT TRY_CONVERT(int, MyVarcharCol)