31

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?

AMIC MING
  • 6,306
  • 6
  • 46
  • 62
Bruce
  • 2,133
  • 12
  • 34
  • 52

6 Answers6

35

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.

Matt
  • 2,982
  • 1
  • 22
  • 23
12

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.

Community
  • 1
  • 1
sgeddes
  • 62,311
  • 6
  • 61
  • 83
  • +1 . . . The first is right. Your answer should be a bit clearer that the second is wrong (I have to scroll to see the "Produces Error"). – Gordon Linoff Feb 06 '13 at 01:55
  • You should also guard against overflows (and optionally permit negative integers): http://stackoverflow.com/a/24250511/1149773 – Douglas Jun 17 '14 at 05:44
9

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.

Michael Erickson
  • 4,217
  • 2
  • 23
  • 10
2

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)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 2
    No, this is wrong. `isnumeric('+') == 1`. `isnumeric('-') == 1`. `isnumeric` is a horrible implementation, and their answer leaves you open to multiple edge cases where a `cast` to `int` will fail despite your check. – mattmc3 Oct 11 '16 at 20:37
  • @mattmc3 . . Did you notice that the OP accepted essentially this answer. In addition, the conversion to `float` fixes many of those problems (although not with overflow). – Gordon Linoff Apr 10 '17 at 11:48
  • The accepted answer gets the TRY_CAST part right for SQL 2012+. To use ISNUMERIC, you have to do a whole lot of gymnastics to handle edge cases. For example, plug '+' or '-' in as values of "val". The cast to `float` will bomb spectacularly. (^^ not my downvote BTW - I prefer comments to rep damage). This answer gives a way to use `ISNUMERIC` that actually handles edge cases and ensures that you have a proper `TRY_CAST` alternative: http://stackoverflow.com/questions/4522056/how-to-determine-the-field-value-which-can-not-convert-to-decimal-float-int-i – mattmc3 Apr 10 '17 at 14:57
1

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
Anoop Verma
  • 1,495
  • 14
  • 19
  • I like this approach but we can't do this inside of a SELECT statement, can we? – Nate Anderson Nov 12 '14 at 00:30
  • I have looked into this & tested it. It doesn't meet my needs. This only prints certain error info.     ERROR_NUMBER() AS ErrorNumber,     ERROR_STATE() AS ErrorState,     ERROR_SEVERITY() AS ErrorSeverity,     ERROR_PROCEDURE() AS ErrorProcedure,     ERROR_LINE() AS ErrorLine,     ERROR_MESSAGE() AS ErrorMessage; but, not my own error message that will identify the record. – givonz Dec 14 '22 at 18:16
0

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'
cmdr_putin
  • 1
  • 2