Here is a simple method to convert a text string to numbers (or an integer). This method evaluates each value in a column to determine if the datatype is numeric. If it is, then it converts it to an integer.

SELECT
	CASE WHEN
		ISNUMERIC(PostalCode) > 0
	THEN CAST(PostalCode AS INT)
	ELSE 0 END
FROM SalesLT.Address

Now, what if the column values contain text characters and you want to strip those characters out and then convert the value to an integers. Well, luckily, there is a way to do this. Most of the examples to do this will loop the values using a while loop, however this example uses a dynamic numbers table in conjunction with a trick shown to me by Simon Sabin from his blog. This method will replace all the non numeric characters and convert the values to an integer.

-- define max number of character values
-- in the string being evaluated
DECLARE @MaxNumber INT = 5000

;WITH Numbers AS
(
    SELECT 1 AS Num
    UNION ALL
    SELECT Num+1
    FROM Numbers
    WHERE Num <= @MaxNumber
)

SELECT
	CAST
	(
		(
			SELECT CASE
				WHEN SUBSTRING(PostalCode,Num,1) LIKE '[0-9]'
				THEN SUBSTRING(PostalCode,Num,1)
				ELSE '' END
			FROM Numbers
			WHERE Num <= LEN(PostalCode)
			FOR XML PATH('')
		) AS int
	 )
FROM SalesLT.Address

OPTION(MAXRECURSION 32767)

To make this work with your example, simply replace the value [PostalCode] with your field and the FROM Clause should be your table you are querying from.

• sql server convert string to int
• sql convert string to int
• SQL convert text to number
• t-sql convert string to int
• CONVERT STRING TO INT IN SQL SERVER 2008