Best equivalent for IsInteger in SQL Server

Question

What is the best way to determine whether or not a field's value is an integer in SQL Server (2000/2005/2008)?

IsNumeric returns true for a variety of formats that would not likely convert to an integer. Examples include '15,000' and '15.1'.

You can use a like statement but that only appears to work well for fields that have a pre-determined number of digits...

select * where zipcode like '[0-9][0-9][0-9][0-9][0-9]'

I could write a user defined function that attempts to convert a varchar parameter to an int within a try/catch block but I'm checking with the community to see if someone has come across any succient methods to achieve this goal - preferably one that can be used within the where clause of a SQL statement without creating other objects.

Answer 1

1 approach is

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

Double negatives, got to love 'em!

Answer 2

If SQL Server 2005+, I'd enable CLR and create the function to support regexes. For SQL Server 2000, see this article for creating a UDF to do the same thing.

Then I'd use the regex: ^\d{5}$

Answer 3

Maybe you should only store integer data in integer datatypes.

Answer 4

Late entry that handles negative

ISNUMERIC(zipcode + '.0e0') --integer
ISNUMERIC(zipcode + 'e0')  --decimal

For more see this