Validating if a value is an integer can be done by using TRY_CAST in SQL Server 2012 or a combination of ISNUMERIC and cast to float in <= SQL Server 2008.

declare @value as varchar(255) = '1.6'

-- SQL Server 2008
select case        when isnumeric(@value) = 1
                then 'Cast succeeded, value is: ' + cast(cast(cast(@value as float) as int) as varchar(255))
                else 'Cast failed.'
       end as    Result

-- SQL Server 2012
select    case    when try_cast(@value as float) is not null 
                then 'Cast succeeded, value is: ' + cast(cast(cast(@value as float) as int) as varchar(255))
                else 'Cast failed.'
        end as    Result

image

 

If you use try_cast(@value as int) the cast will fail on a float ‘1.6’, like:

 

image

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.