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

Late entry that handles negative

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

For more see this

Answer 2

1 approach is

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

Double negatives, got to love 'em!

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

This expression gives 1 for an integer value and 0 otherwise

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

Answer 5

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 6

It looks like this question needs an updated answer.

Limiting the answer to the question title:

where ISNUMERIC(zipcode) = 1
  and zipcode - FLOOR(zipcode) = 0

Expounding based on the text of the question...

Currently-supported versions of SQL Server all support/include the TRY-CONVERT function.

declare @a varchar(100)
set @a = '-1.2a'
--set @a = '-1.2'
--set @a = '-1'
--set @a = '-1.0'
--set @a = '-0'
--set @a = '0'
--set @a = '1'

select @a as 'Value'
, ISNUMERIC(@a) as ISNUMERIC
, case when ISNUMERIC(@a) = 1 and @a - FLOOR(@a) = 0 then 1 else 0 end as ISINTEGER
, case when try_convert(int, @a) >= 0 and left(@a, 1) <> '-' then 1 else 0 end as ISWHOLENUMBER
, case when try_convert(int, @a) > 0 then 1 else 0 end as ISCOUNTINGNUMBER

You'll notice that TRY_CONVERT(INT, -1.0) returns NULL. So TRY_CONVERT(INT, @a) IS NOT NULL is not quite right for ISINTEGER.

case when ISNUMERIC(@a) = 1 and @a - FLOOR(@a) = 0 then 1 else 0 end as ISINTEGER

...works because if ISNUMERIC(@a) = 1 is false, FLOOR(@a) is not evaluated. Reversing the order...

case when  @a - FLOOR(@a) = 0 and ISNUMERIC(@a) = 1 then 1 else 0 end as ISINTEGER

...generates an error when the value (@a) is not numeric.

So, for the case of zipcode, assuming you want to verify that a 5-digit zip code is a number and it must be 5 digits (so it can't be zero or less) and would never contain a decimal point (so you don't need to know if 12345.000 is an integer):

where try_convert(int, zipcode) > 0
  and len(zipcode) = 5

Answer 7

I came up with the perfect answer for this on another StackO question.
It also proves you cannot use ".0e0" like one user suggests here.
It does so without CLR or non-scalar functions.
Please check it out: https://stackoverflow.com/a/10645764/555798

Answer 8

After moving to sql 2008, I was struggling with isnumeric('\8') returning true but throwing an error when casting to an integer. Apparently forward slash is valid currency for yen or won - (reference http://www.louiebao.net/blog/200910/isnumeric/)

My solution was

case when ISNUMERIC(@str) > 0 and not rtrim(@str) LIKE '[^0-9]%'  and not rtrim(@str) LIKE '%[^0-9]' and not rtrim(@str) LIKE '[^0-9]%' then rtrim(@str) else null end

Answer 9

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 10

I did it using a Case statement: Cast(Case When Quantity/[# of Days]= Cast(Quantity/[# of Days] as int) Then abs(Quantity/[# of Days]) Else 0 End as int)

Answer 11

To test whether the input value is an integer or not we can use SQL_VARIANT_PROPERTY function of SQL SERVER.

The following SQL Script will take input and test it whether the data type turns out to be integer or not

declare @convertedTempValue bigint, @inputValue nvarchar(255) = '1' --Change '1' to any input value
set @convertedTempValue = TRY_PARSE(@inputValue as bigint) --we trying to convert to bigint
declare @var3 nvarchar(255) = cast (SQL_VARIANT_PROPERTY(@convertedTempValue,'BaseType') as nvarchar(255)) --we using SQL_VARIANT_PROPERTY to find out datatype
if ( @var3 like '%int%')
    begin
    print 'value is integer'
    end
else
    begin
    print 'value is non integer'
    end
go

Answer 12

Really late to this but would this work?

select * from from table 
where (ISNUMERIC(zipcode) = 0 OR zipcode like '%.%')

Filters out items that are integers.

Answer 13

Maybe you should only store integer data in integer datatypes.