IsNumeric(), the unloved system function.

The system function IsNumeric() is so unloved that I feel compelled to say something in its’ defence. It is more or less giving you the correct answer, but to a different question to the one that you’re asking, and what it tells you isn’t very helpful.

IsNumeric() is actually answering the question ‘Can this string be converted or ‘cast’ to a numeric datatype by SQL Server?’ It is pretty good at getting this answer right but it doesn’t tell you which datatype. It also isn’t interested in such things as overflow.

This problem of IsNumeric() was solved in SQL Server 2012. There was no way of morphing IsNumeric() intro something more valuable, so Try_Cast(), Try_Parse(), and Try_Convert() were introduced. The introduction of these system functions really solve the problem, unless you are still on earlier versions than SQL Server 2012. I’ll show how to do the same thing in previous versions of SQL Server, and demonstrates one or two tricks you can do with these functions.

So how bad is IsNumeric()?

To answer this question we first need some test data, a table full of strings that might, or might not, be numbers

Now, we can check to see if they really are numbers, or just strings by seeing whether the Try_Convert() function was successful or not.

You’ll see how a surprising number of these can be converted into numbers.

Now we can see how well IsNumeric() gets it right.

So the developers who complained have some justice, but in fact the errors are pretty minor, judging from my test data. IsNumeric() isn’t aware that empty strings are counted as (integer)zero, and that some perfectly valid money symbols, by themselves, are not converted to (money)zero. Plenty of others are, as you’ll see from the test data. This looks to me to be a sign that they simply haven’t updated the function to accommodate the full Unicode money types.

Even if you just wanted a simple yes/no answer about ‘whether a string can be converted to a number, but which datatype we’re not telling you’, then it is more reliable to use …

…and you can hone this expression to your exact requirements. This will, for example, not tell you if it is a BIGINT or a NUMERIC(38,24) though, because it tests for overflow.

But I’m on an old version of SQL Server!!

No problem at all. We with grizzled muzzles have been avoiding IsNumeric() for many years and you can use the following code with any old version of SQL Server.

This produces the same result with our test data as the Try_Convert() equivalent. You’ll notice that there are four different tests which you can use, for an integer, numeric, float and money. You can now play along even on an old version of SQL Server but beware! As we soon show, the TRY_ family of functions also check for overflow.

We can now provide a way of making sense of a very dodgy data import without risk of errors and can even filter out the data entries that need to be corrected.

When you execute this, you’ll see that some strings will successfully convert to a number type other than integer, which can then be converted to an integer. The same is true in other directions. If, therefore, you want to get the maximum of success in your data you will need to put in some logic to convert first to the most accommodating format and thence to your target format. You could lose precision or get an overflow error! You’ll also want to define the type of integer and numeric to correspond with your requirements. Things can get more complex when you use Try_Parse() to import dates.

Here is an obvious example where such a tactic is excellent and appropriate. A lot of integer data comes from the real world with commas. Rather than strip them out, you can parse them.

Another great advantage of the Try_Convert(), Try_Cast() and Try_Parse() datatypes over IsNumeric() is that they check for overflow as well as well as whether it represents a number of that datatype. This test confirms it

To use the wildcard matches for this purpose, as in our previous example, isn’t possible. We can get a rough idea by counting the digits and then matching the most significant digit, but it isn’t that accurate!

BIGINTs range from -9,223,372,036,854,775,808) to 9,223,372,036,854,775,807, INTEGERs go from -2,147,483,648 to 2,147,483,647, whereas SMALLINTs range from -32,768 to 32,767 Bytes and lastly TINYINTs just go from 0 to 255. This means that you’d have to construct a string check accordingly, remembering the complications of sign. It would usually be simpler to upgrade to SQL Server 2012!

But I want to use my own rules!

You’ll agree that IsNumeric() is liberal with what it considers to be an Integer, Numeric, Float or Money datatype. This is because TRY_Convert(), Try_Parse() and Try_Cast() all obey more or less the same liberal rules. If you want something stricter, then you’ll need to combine the LIKE wildcard techniques with TRY_Convert(). As an example, this query  disallows blank strings or + signs as meaning zero as well as anything else that cannot be converted into an integer.

 Likewise, you might feel it unreasonable that an amount should be specified entirely without any number, just a currency sign. That is cool.  This would do the trick

I hope that this makes the point that when you are filtering your input to ensure that correctly formed numbers are imported and anything that isn’t right is saved for amendment, you get a large measure of control by combining the two techniques

Dates

I haven’t mentioned dates, because this article is on the theme of IsNumeric() but the Try_Convert() function is a wonderful way of reading dates that are in a number of different formats. The SQL is scary, but so is the task of inputting dates successfully into SQL Server. This shows how it is done, but you may wish to vary the order in which you do the attempts according to your culture. What it shows is how you can do quite well with Try_Parse, even better with Try_Convert. But you can ace it with a coalesce function with a series of attempts. You would order those Try_Convert attempts in decreasing order of likelihood and with the correct orientation of the year for your culture and data before you try the reverse!