How to convert Varchar to Int in sql server 2008?

Spaces will not be a problem for cast, however characters like TAB, CR or LF will appear as spaces, will not be trimmed by LTRIM or RTRIM, and will be a problem.

For example try the following:

declare @v1 varchar(21) = '66',
        @v2 varchar(21) = '   66   ',
        @v3 varchar(21) = '66' + char(13) + char(10),
        @v4 varchar(21) = char(9) + '66'

select cast(@v1 as int)   -- ok
select cast(@v2 as int)   -- ok
select cast(@v3 as int)   -- error
select cast(@v4 as int)   -- error

Check your input for these characters and if you find them, use REPLACE to clean up your data.

---

Per your comment, you can use REPLACE as part of your cast:

select cast(replace(replace(@v3, char(13), ''), char(10), '') as int)

If this is something that will be happening often, it would be better to clean up the data and modify the way the table is populated to remove the CR and LF before it is entered.