Spaces will not be a problem for
cast, however characters like
LF will appear as spaces, will not be trimmed by
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
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
LF before it is entered.
~ Answered on 2011-11-01 21:55:05