Best equivalent for IsInteger in SQL Server

By | December 6, 2017
Questions:

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.

Answers:

1 approach is

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

Double negatives, got to love ’em!

Questions:
Answers:

Late entry that handles negative

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

For more see this

Questions:
Answers:

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}$

Questions:
Answers:

This expression gives 1 for an integer value and 0 otherwise

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

Questions:
Answers:

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

Questions:
Answers:

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

Questions:
Answers:

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

Questions:
Answers:

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

Questions:
Answers:

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)

Questions:
Answers:

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

Questions:
Answers:

Maybe you should only store integer data in integer datatypes.

Leave a Reply

Your email address will not be published. Required fields are marked *