Best Equivalent for Isinteger in SQL Server

Best equivalent for IsInteger in SQL Server

1 approach is

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

Double negatives, got to love 'em!

T-sql - determine if value is integer

Here's a blog post describing the creation of an IsInteger UDF.

Basically, it recommends adding '.e0' to the value and using IsNumeric. In this way, anything that already had a decimal point now has two decimal points, causing IsNumeric to be false, and anything already expressed in scientific notation is invalidated by the e0.

Check if field is numeric, then execute comparison on only those field in one statement?

does this work for you?

select * from purchaseorders
where (case when IsNumeric(purchase_order_number) = 1
       then cast(purchase_order_number as int)
       else 0 end) >= 7

SQL Server : How to test if a string has only digit characters

Use Not Like

where some_column NOT LIKE '%[^0-9]%'

Demo

declare @str varchar(50)='50'--'asdarew345'

select 1 where @str NOT LIKE '%[^0-9]%'

SQL LIKE condition to check for integer?

That will select (by a regex) every book which has a title starting with a number, is that what you want?

SELECT * FROM books WHERE title ~ '^[0-9]'

if you want integers which start with specific digits, you could use:

SELECT * FROM books WHERE CAST(price AS TEXT) LIKE '123%'

or use (if all your numbers have the same number of digits (a constraint would be useful then))

SELECT * FROM books WHERE price BETWEEN 123000 AND 123999;

Check if a string contains only number

I would suggest try_convert():

select (case when try_convert(col, float) is not null then 'valid' else 'invalid' end)

The one possible downside is exponential format; 1e6 is a valid number for instance.

An alternative is the where approach; you just need more complete logic:

select (case when col like '%[^0-9.]%' then 'invalid'
             when col like '%.%.%' then 'invalid'
             else 'valid'
        end)

ISNumeric is buggy in SQL Server?

0E1 is numeric. http://en.wikipedia.org/wiki/Exponential_notation#E_notation

To allow only integers containing only numbers, see Best equivalent for IsInteger in SQL Server

sql server - check to see if cast is possible

Well, in SQL Server 2012 you could use the new TRY_CAST(), but with SQL Server 2008, you should be able to use ISNUMERIC(), and then include handling for values that do not pass that test.

SQL where clause with one parameter to check either int or string field

You can't have none integer values in your parameter so have changed to varchar.

Alter Procedure [dbo].[getClassInfo] ( @ClassId Varchar(50) )
As
    Begin
        If IsNumeric(@ClassId + '.0e0') = 1
            Begin
                Declare @ClassIdInt Int = Convert(Int , @ClassId);

                Select  [usergroupId]
                      , [gName]
                      , [accessCode]
                From    [dbo].[usergroup]
                Where   @ClassIdInt = [usergroupid];
            End; 
        If IsNumeric(@ClassId + '.0e0') = 0
            Begin
                Select  [usergroupId]
                      , [gName]
                      , [accessCode]
                From    [dbo].[usergroup]
                Where   [accessCode] = @ClassId;
            End;
    End;

The if statement determines if the value passed is an integer and then runs the appropriate query

---

Related Topics