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.