http://codesimplified.com/2012/05/18/what-is-wrong-with-isnumeric/

Actually, this is a bit wrong. ISNUMERIC does exactly what it was designed to do. It does NOT "falsely" interpret letters and symbols. It returns a "1" if the data can be converted to ANY numeric datatype. That includes things that have "white-space" like tabs in them. It includes certain letters like "E" and "D" for different forms of scientific notation. It includes certain punctuation like commas and decimal points. It includes signs like "+", '-', currency symbols, and parenthesis for certain forms of negative numbers. Etc, etc.

The REAL problem with ISNUMERIC is what people "think" ISNUMERIC should mean. It certainly DOESN'T mean "IsAllDigits" or "IsADecimalNumber" or "IsAnInteger". It simply means that the character can be converted to some form of numeric datatype even if it's not the datatype you're trying to check for.

For more information, please see the following article. http://www.sqlservercentral.com/articles/IsNumeric/71512/

If you want to test for integers, try IsNumeric(@stringvalue + '.0E0')

Two excellent writeups on that subject:
A CLR Alternative To The SQL Server ISNUMERIC Function:
http://visualstudiomagazine.com/articles/2011/10/11/a-clr-alternative-to-isnumeric.aspx:

Why doesn't ISNUMERIC work correctly?:
http://www.sqlservercentral.com/articles/IsNumeric/71512/

A CLR Alternative To The SQL Server ISNUMERIC Function:

I'd like to express a word of caution here...

I realize that VB and C# are both compiled languages that can be made to run at near machine language speeds. The problem is that calling CLRs does have some overhead and not everyone writes good VB or C#. CLR is not the performance panacea that so many make it out to be.

On the flip side, there are a large number of places where SQL Server will also run at near machine language speeds. Just because it's an "interpreted" language, doesn't mean that it can't use objects that have been compiled to run at near machine language speeds.

To wit and quite contrary to urban legend, there are a lot of places where doing something in T-SQL will actually blow the doors off a CLR equivalent. Just because something is written as a CLR doesn't mean the CLR will automatically win the proverbial performance or resource footrace.

Even if a CLR does end up beating T-SQL in one fashion or another, you might also want to ask yourself if it's going to be worth maintaining code in two places. For example if T-SQL takes 2 seconds to do something to a million rows and the CLR takes 1 second to do the same thing to those million rows, is the CLR worth it? Of course, Only you and your team can answer that.

My suggestion is that before you implement any CLR in production, that you do performance, resource, scalability testing, and a little checking on reality. Depending on what you're doing, you may have a huge surprise and sudden appreciation for what T-SQL can actually do for you. Usually with T-SQL, all you have to do is not suck real bad at using it. As with all else in SQL Server, "It Depends" ;-)

If you want to test for integers, try IsNumeric(@stringvalue + '.0E0')

That's some very clever and simple looking code. Let's try it and compare it to a more traditional method. {EDIT} Just to be clear. Eric's code does a test for integers... mine only does a test for "IsAllDigits" which also happens to work for unsigned postive integers. There is such a thing as negative integers. I apologize for mistakenly saying mine worked for integers especially since I made no such claim in the article that links were previously provided for.

--===== Conditionally drop the test table to make reruns easier in SSMS
     IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
        DROP TABLE #TestTable
;
--===== Create and build the test table on-the-fly.
 SELECT TOP 1000000
        SomeString = SUBSTRING(CAST(NEWID() AS CHAR(36)),15,4)
   INTO #TestTable
   FROM sys.all_columns ac1,
        sys.all_columns ac2
;
GO
--===== Test the traditional method for finding strings containing only digits
    SET STATISTICS TIME OFF;
  PRINT '========== Somestring NOT LIKE ''%[^0-9]%'' ============================';
DECLARE @BitBucket INT;
    SET STATISTICS TIME ON;
 SELECT COUNT(*)
   FROM #TestTable 
  WHERE Somestring NOT LIKE '%[^0-9]%'
;
GO
--===== Test the "try this" method for finding integers.  This IS different than finding just all digits.
    SET STATISTICS TIME OFF;
  PRINT '========== ISNUMERIC(SomeString+''.0E0'') ================================';
DECLARE @BitBucket INT;
    SET STATISTICS TIME ON;
 SELECT COUNT(*) 
   FROM #TestTable 
  WHERE ISNUMERIC(SomeString + '.0E0') = 1
;
GO
SET STATISTICS TIME OFF;

Here are the results on my 10 year old desk top. Your results will be different but I believe you'll find that the traditional method always beats the second because string concatenation is fairly expensive. {EDIT} If you're looking for "IsAllDigits", then you might want to use the first code. If you're actually looking for INTEGERS which can contain negative numbers, then Eric's code (the second code) is the easier and faster way to go.

(1000000 row(s) affected)
========== Somestring NOT LIKE '%[^0-9]%' ============================

(1 row(s) affected)

SQL Server Execution Times:
   CPU time = 1250 ms,  elapsed time = 1460 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
========== ISNUMERIC(SomeString+'.0E0') ================================

(1 row(s) affected)

SQL Server Execution Times:
   CPU time = 1922 ms,  elapsed time = 2305 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

In fact, now that I've provided a test gig, someone else run the test and include a CLR function in a similar manner. Let's find out if the CLR would be faster in this case.

@Jeff,

The idea about appending a tail with isnumeric is a general one that works for more situations than just integers—such as testing for decimals (@string + 'e0').

But more importantly, '-1' and '$1' are integers, too. What performance do you get if you accommodate those using pattern matching?

If you don't care about these, then of course just checking for only digits is fine. There may be additional non-digit decorations on numbers that do not prevent them from being classified as integers.

Crud... thanks for the catch, Erik. I apologize for saying that mine was for "integers" because I certainly didn't mean that. It's not. Mine was only for "IsAllDigits" and by the time this post clears, I'll hope to have corrected my previous post. I don't even come close to making such a claim in the article someone provided a link for. Not sure why I said "Integer" because it sure isn't.

Actually, to my amusement mine is not for integers either as '$1e0' is not considered numeric. But '-1e0' is.

The lesson? Know your tools!

heh... that's alright. I don't consider $1 to be an integer. It's a whole dollar which is currency. I know... semantics, but, yes, I consider your code to be good for determining if something is an integer or not.