sql server - check to see if cast is possible

Question

I have the following code to cast nvarchar to integer:

     cast(@value as int)

However I have no control of the parameter @value, hence the code might fail. Is there anyway to check if a cast is possible before doing a cast?

Answer 1

Well, in SQL Server 2012 you could use the new TRY_CAST(), but with SQL Server 2008, you should be able to use ISNUMERIC(), and then include handling for values that do not pass that test.

Answer 2

I've recently answered a question about this and using ISNUMERIC to CAST to an INT won't work by itself. Reason being, ISNUMERIC returns true for non integer numbers (1.5) for example.

Here was a recent answer on the subject:

https://stackoverflow.com/a/14692165/1073631

Consider adding an additional check using CHARINDEX with ISNUMERIC, or what I prefer, use a Regular Expression to validate the data.

And here is a Fiddle demonstrating the problem with using ISNUMERIC on it's own. And the Fiddle using a regular expression instead that works.

DECLARE @Test nvarchar(10)
SET @Test = '1.5'
--Works
SELECT CASE WHEN @Test NOT LIKE '%[^0-9]%' THEN CAST(@Test as int) ELSE 0 END 
-- Produces Error
SELECT CASE WHEN ISNUMERIC(@Test) = 1 THEN CAST(@Test as int) ELSE 0 END 

Good luck.

Answer 3

I generally use the following, it seems to cover all the situations.

SELECT CASE WHEN 1 = ISNUMERIC(@value + '.0') THEN CAST(@value as int) ELSE 0 END

It takes advantage of the fact that "ISNUMERIC" will not allow two periods. The "TRY_CAST" in SQL Server 2012+ is a much better solution though.

Answer 4

The proper test is:

select (case when isnumeric(val) = 1 and val not like '%e%' and val not like '%.%'
             then cast(val as int)
        end)

The function isnumeric() returns 1 for anything that looks like a float, so you have to be careful.

You can also use what I consider to be a peculiarity of SQL Server. You can cast the floating value 1.23 to an int, but you cannot cast the string value. So, the following also works:

select (case when isnumeric(val) = 1
             then cast(cast(val as float) as int)
        end)