Is ISNUMERIC numeric?

In SQL, if you’re not sure if a value can be cast to a certain datatype there are some built-in functions that can help you out.
Take the following construction, where col_Value is a CHAR(4):

   1: SELECT CASE ISNUMERIC (col_Value)

   2:             WHEN 1 THEN CAST (col_Value AS INT)

   3:             ELSE NULL

   4:             END

At first glance, this solution seems to handle col_Value just fine. If col_Value contains a numeric value it will be cast to an INT and if it’s not, a NULL value will be returned.

The developers expectations seem to be met: in this scenario no typecasting errors can occur. Or can they?

Truth is, the ISNUMERIC function is often misinterpreted (at least, that’s my personal experience).

According to MSDN the definition of ISNUMERIC is: Determines whether an expression is a valid numeric type.

ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type.

The catch is, a returnvalue of 1 means that the expression can be cast to at least one numeric datatype, rather than all available numeric datatypes supported by SQL Server.

The following queries illustrate this behavior:

   1: SELECT ISNUMERIC ('100')        -- Returns 1

   2:  

   3: SELECT CAST ('100' AS FLOAT)    -- Returns 100

   4: SELECT CAST ('100' AS NUMERIC)  -- Returns 100

   5: SELECT CAST ('100' AS MONEY)    -- Returns 100,00

   6: SELECT CAST ('100' AS INT)      -- Returns 100

   7:  

   8:  

   9: SELECT ISNUMERIC ('1e2')        -- Returns 1

  10:  

  11: SELECT CAST ('1e2' AS FLOAT)    -- Returns 100

  12: SELECT CAST ('1e2' AS NUMERIC)  -- Error converting data type varchar to numeric.

  13: SELECT CAST ('1e2' AS MONEY)    -- Cannot convert a char value to money. The char value has incorrect syntax.

  14: SELECT CAST ('1e2' AS INT)      -- Conversion failed when converting the varchar value '1e2' to data type int

  15:  

  16: SELECT ISNUMERIC ('€100')       -- Returns 1

  17:  

  18: SELECT CAST ('€100' AS FLOAT)   -- Error converting data type varchar to float.

  19: SELECT CAST ('€100' AS NUMERIC) -- Error converting data type varchar to numeric.

  20: SELECT CAST ('€100' AS MONEY)   -- Returns 100,00

  21: SELECT CAST ('€100' AS INT)     -- Conversion failed when converting the varchar value '€100' to data type int.

So in conclusion; if an expression has to be cast into a specific numeric datatype, checking ISNUMERIC may not be sufficient. This is of course heavily dependant on the possible values of the expression, but if this is unpredictable extra checks may be necessary