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

This expression gives 1 for an integer value and 0 otherwise

floor((floor(abs(zipcode)))/abs(zipcode))

Answer 2

Why not just use the following? I can't see to find any cases where it fails.

• 1 = integer
• 0 = not integer
• null = non-numeric

DECLARE @TestValue nvarchar(MAX)
SET @TestValue = '1.04343234e5'

SELECT CASE WHEN ISNUMERIC(@TestValue) = 1
        THEN CASE WHEN ROUND(@TestValue,0,1) = @TestValue
            THEN 1
            ELSE 0
            END
        ELSE null
        END AS Analysis

Answer 3

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 4

See whether the below code will help. In the below values only 9, 2147483647, 1234567 are eligible as Integer. We can create this as function and can use this.

CREATE TABLE MY_TABLE(MY_FIELD VARCHAR(50))
INSERT INTO MY_TABLE
VALUES('9.123'),('1234567'),('9'),('2147483647'),('2147483647.01'),('2147483648'), ('2147483648ABCD'),('214,7483,648')

SELECT *
FROM MY_TABLE
WHERE CHARINDEX('.',MY_FIELD) = 0 AND CHARINDEX(',',MY_FIELD) = 0       
AND ISNUMERIC(MY_FIELD) = 1 AND CONVERT(FLOAT,MY_FIELD) / 2147483647 <= 1
DROP TABLE MY_TABLE

Answer 5

Maybe you should only store integer data in integer datatypes.