Best equivalent for IsInteger in SQL Server

regex, sql, sql-server, t-sql

Solution

1 approach is

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

Double negatives, got to love 'em!

Problem

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.

Original source

Related problems