SQL Server 2008 doesn’t yet have the TRY_PARSE function, so safely converting text to an integer is not as simple as you’d think.
There’s the ISNUMERIC function, but as people have pointed out,
there are major issues with this function, and it doesn’t work for regular people like you
and me. The real solution seems to be to add
.e0 to the end of the string before checking,
which handles these funky scenarios.
select cast(Val as int) [TheIntVal] from dbo.MyTable where isnumeric(Val + '.e0') = 1;