sql server - check to see if cast is possible

问题:

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?

回答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.

回答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.

回答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.

回答4:

Maybe we can do something like this:

declare @value as nvarchar(10) = 'A';

begin try
    select cast(@value as int);
end try
begin catch
-- do something
end catch

回答5:

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)

回答6:

Use a procedure with a TRY CATCH block to suppress errors

i.e.

CREATE PROCEDURE p_try_cast
 @type nvarchar(MAX),
 @value nvarchar(MAX)
AS
BEGIN

    BEGIN TRY
        DECLARE @sql        varchar(MAX)
        DECLARE @out_table  TABLE(value varchar(MAX))

        SET @sql = 'SELECT  CONVERT(varchar(max), CAST(''' + @value + ''' AS ' + @type + '))'

        INSERT  @out_table
        EXECUTE (@sql)

        IF EXISTS ( SELECT 1 FROM @out_table WHERE value = @value)
            RETURN 1

        RETURN 0
    END TRY
    BEGIN CATCH
        RETURN 0
    END CATCH

END

GO

Now you can call that with the passed string and desired type and the proc returns 1 for success and 0 for failure

DECLARE @ret int

-- This returns 0 - Fail
EXEC @ret = p_try_cast 'integer', '1.5'

-- This returns 1 - Success
EXEC @ret = p_try_cast 'integer', '1.5'

-- This returns 0 - Fail
EXEC @ret = p_try_cast 'char(4)', 'HELLO'

-- This returns 1 - Success
EXEC @ret = p_try_cast 'char(4)', 'HELL'