Efficient ISNUMERIC() replacements on SQL Server?

Solution 1:

You can use the T-SQL functions TRY_CAST() or TRY_CONVERT() if you're running SQL Server 2012 as Bacon Bits mentions in the comments:

SELECT CASE WHEN TRY_CAST('foo' AS INT) IS NULL THEN 0 ELSE 1 END

SELECT CASE WHEN TRY_CAST(1 AS INT) IS NULL THEN 0 ELSE 1 END

If you're using SQL 2008 R2 or older, you'll have to use a .NET CLR function, and wrap System.Decimal.TryParse().

Solution 2:

Depending on the circumstances and the performance characteristics of the validation, I sometimes use a variation of the LIKE expression instead. For example:

NOT LIKE '%[^0-9]%'

Note that this specific example is fairly naive. It doesn't guarantee that the value is valid for conversion to a particular data type. It also doesn't allow for +/- signs or decimal points if you need those.

Solution 3:

Another option might be to write an extended stored procedure in a language such as C, make it into a DLL and make it available to SQL Server.

I don't imagine it would take too many lines of code to do, and it probably would be faster than writing a Managed Stored Procedure in .NET, because you wouldn't incur the extra overheard from loading the CLR.

Here's a tidbit of information: http://msdn.microsoft.com/en-us/library/ms175200.aspx

Here's some C++ code that might work for you:

using namespace std;

int checkNumber() {
  int number = 0;
  cin >> number;
  cin.ignore(numeric_limits<int>::max(), '\n');

  if (!cin || cin.gcount() != 1)
    cout << "Not a number.";
  else
    cout << "Your entered: " << number;
  return 0;
}

Solution 4:

Following the .NET CLR route, you could go with a regular expression, particularly if you expect a certain range of numbers.

SQL 2005 and Regular Expressions

Solution 5:

Generally as a practice, I try not to let untyped data into the database, as it is better more suited to either handle it at the application layer, or for batch imports handle it in SQL Integration Services so that the data comes in typed correctly from the start.

I have had to do it many times in the past and usually the fastest way is to write your own user defined function to verify the data is in the format you expect, as most of the time the overhead to calling out to an extended stored proc or managed code for simple validation is slower than just doing it in T-SQL.