Stop using ISNUMERIC, it’s (probably) wrong

Even before SQL Server 2012, ISNUMERIC was a function that you best avoided. Put quite simply, it's just bad. It often provides results that are wrong, and it isn't data type specific, something that's actually really important when you have several numerical data types to use: int/bigint and the smaller ones, numeric/decimal, float/real and money. All of these data types behave very differently when converting from a varchar.

Let's start by looking at the results for ISNUMERIC:

CREATE TABLE dbo.BadNumbers(Number varchar(20));

INSERT INTO dbo.BadNumbers
VALUES('1'),
      ('7.8'),
      (''),
      ('1,020'),
      ('1,079,190.7'),
      ('17,1,68,11.0'),
      ('1/3'),
      ('1.236E7'),
      ('2d6');


SELECT Number,
       ISNUMERIC(Number) AS IsNumber
FROM dbo.BadNumbers;

And the results are below:

Number        |IsNumber
-----------------------
1             |1
7.8           |1
{blank string}|0
1,020         |1
1,079,190.7   |1
17,1,68,11.0  |1
1/3           |0
1.236E7       |1
2d6           |1

Notice that of all the values, only 2 came back as not being numerical values, '' and '1/3', according to ISNUMERIC. Otherwise, all the values are apparently completely valid numbers. As the failures are the smaller set, we'll start with looking at those.

If you try converting the value of '' to a numerical value, you'll actually find that it works for most of them. SELECT CONVERT(int,'') and SELECT CONVERT(float,''), for example, will both return 0. The only data type it doesn't work for is decimal/numeric. As for '1/3' this does not work for any of the data types. In fact, out of all the values, this is the only value where it was correct for all the data types.

SELECT Number,
       TRY_CONVERT(int,Number) AS [int],
       TRY_CONVERT(decimal(38,2),Number) AS [decimal],
       TRY_CONVERT(float,Number) AS [float],
       TRY_CONVERT(money,Number) AS [money]
FROM dbo.BadNumbers
WHERE ISNUMERIC(Number) = 0;

Giving the results:

Number        |int    |decimal    |float    |money
--------------|-------|-----------|---------|------------
{blank string}|0      |NULL       |0        |0.0000
1/3           |NULL   |NULL       |NULL     |NULL

Now, for the ones that were marked as valid numerical values. Well here I'm also going to introduce you to a function that was introduced in SQL Server 2012, TRY_CONVERT (and it's friend TRY_CAST. These functions work identically to their counterparts, CONVERT (and CAST), apart from that if the conversion fails instead of an error being generated, the value NULL will be returned from the function.

So let's put that into practice and test that function again those that were flagged as valid numerical values:

SELECT Number,
       TRY_CONVERT(int,Number) AS [int],
       TRY_CONVERT(decimal(38,2),Number) AS [decimal],
       TRY_CONVERT(float,Number) AS [float],
       TRY_CONVERT(money,Number) AS [money]
FROM dbo.BadNumbers
WHERE ISNUMERIC(Number) = 1;

And this gives the below results:

Number        |int    |decimal    |float    |money
--------------|-------|-----------|---------|------------
1             |1      |1.00       |1        |1.0000
7.8           |NULL   |7.80       |7.8      |7.8000
1,020         |NULL   |NULL       |NULL     |1020.0000
1,079,190.7   |NULL   |NULL       |NULL     |1079190.7000
17,1,68,11.0  |NULL   |NULL       |NULL     |1716811.0000
1.236E7       |NULL   |NULL       |12360000 |NULL
2d6           |NULL   |NULL       |2000000  |NULL

Very quickly you can see the how much of a difference the data type makes. For int there is only 1 value that could be converted ('1'), where as for money there were 5 values that were successfully converted. ISNUMERIC doesn't care about what numerical data type you might be converting to, it's just saying it will (probably) convert to one of them. Considering, however, that SQL Server has 4 different type of numerical data types to choose from, this far from means the value you have will convert to the data type you are using.

If you are, therefore, trying to check if a value is a valid for a numerical data type, then use TRY_CONVERT/TRY_CAST. The correct clause would be WHERE TRY_CONVERT(int,YourString) IS NOT NULL. Of course, I don't recommend it's use in a WHERE (as it'll make the query non-SARGable) but you could use it in a different comparison expression, such as a CASE. It would, however, be pointless have an expression like CASE WHEN TRY_CONVERT(int,MyColumn) IS NOT NULL THEN CONVERT(int,MyColumn) END; just use TRY_CONVERT on it's own.