10 January, 2014
0 Comments
1 category
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
If you use try_cast(@value as int) the cast will fail on a float ‘1.6’, like:
Tags: T-SQL
Category: Uncategorized