Conversion from varchar to int (integer) can be done using CAST or CONVERT.
Below peice of code converts varchar to int using CAST.
[sql]
DECLARE @intVariable INT
DECLARE @VarcharVariable VARCHAR(10)
SET @VarcharVariable = ‘99999’
–Converting VARCHAR TO INT Using CAST
SET @intVariable = CAST(@VarcharVariable as int)
SELECT @intVariable , @VarcharVariable
[/sql]
Below peice of code converts varchar to int using CONVERT.
[sql]
DECLARE @intVariable INT
DECLARE @VarcharVariable VARCHAR(10)
SET @VarcharVariable = ‘999999’
–Converting VARCHAR TO INT Using Convert
SET @intVariable = CONVERT(int,@VarcharVariable)
SELECT @intVariable , @VarcharVariable
[/sql]
If you have non numeric characters use this link to convert string to number:
http://sqlserverlearner.com/2012/script-to-strip-non-numeric-characters-from-string
Applies to:
SQL Server 2012
SQL Server 2008
SQL Server 2008 r2
SQL Server 2005