[sql-server] T-sql - determine if value is integer

I want to determine if a value is integer (like TryParse in .NET). Unfortunatelly ISNUMERIC does not fit me because I want to parse only integers and not every kind of number. Is there such thing as ISINT or something?

Here is some code to make things clear. If MY_FIELD is not int, this code would fail:

SELECT @MY_VAR = CAST(MY_FIELD AS INT)
FROM MY_TABLE
WHERE MY_OTHER_FIELD = 'MY_FILTER'

Thank you

This question is related to sql-server tsql casting integer isnumeric

The answer is


In his article Can I convert this string to an integer?, Itzik Ben-Gan provides a solution in pure T-SQL and another that uses the CLR.

Which solution should you choose?

Is the T-SQL or CLR Solution Better? The advantage of using the T-SQL solution is that you don’t need to go outside the domain of T-SQL programming. However, the CLR solution has two important advantages: It's simpler and faster. When I tested both solutions against a table that had 1,000,000 rows, the CLR solution took two seconds, rather than seven seconds (for the T-SQL solution), to run on my laptop. So the next time you need to check whether a given string can be converted to an integer, you can include the T-SQL or CLR solution that I provided in this article.

If you only want to maintain T-SQL, then use the pure T-SQL solution. If performance is more important than convenience, then use the CLR solution.

The pure T-SQL Solution is tricky. It combines the built-in ISNUMERIC function with pattern-matching and casting to check if the string represents an int.

SELECT keycol, string, ISNUMERIC(string) AS is_numeric,
  CASE
    WHEN ISNUMERIC(string) = 0     THEN 0
    WHEN string LIKE '%[^-+ 0-9]%' THEN 0
    WHEN CAST(string AS NUMERIC(38, 0))
      NOT BETWEEN -2147483648. AND 2147483647. THEN 0
    ELSE 1
  END AS is_int
FROM dbo.T1;

The T-SQL part of the CLR solution is simpler. You call the fn_IsInt function just like you would call ISNUMERIC.

SELECT keycol, string, ISNUMERIC(string) AS is_numeric,
  dbo.fn_IsInt(string) AS is_int
FROM dbo.T1;

The C# part is simply a wrapper for the .NET's parsing function Int32.TryParse. This works because the SQL Server int and the .NET Int32 are both 32-bit signed integers.

using System;
using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean fn_IsInt(SqlString s)
    {
        if (s.IsNull)
            return SqlBoolean.False;
        else
        {
            Int32 i = 0;
            return Int32.TryParse(s.Value, out i);
        }
    }
};

Please read Itzik's article for a full explanation of these code samples.


I am not a Pro in SQL but what about checking if it is devideable by 1 ? For me it does the job.

SELECT *
FROM table    
WHERE fieldname % 1 = 0

This works fine in SQL Server

SELECT (SELECT ISNUMERIC(2) WHERE ISNUMERIC(2)=1 AND 2 NOT LIKE '%.%')


Use PATINDEX

DECLARE @input VARCHAR(10)='102030.40'
SELECT PATINDEX('%[^0-9]%',RTRIM(LTRIM(@input))) AS IsNumber

reference http://www.intellectsql.com/post-how-to-check-if-the-input-is-numeric/


Why not just do something like:

CASE
WHEN ROUND(MY_FIELD,0)=MY_FIELD THEN CAST(MY_FIELD AS INT)
ELSE MY_FIELD
END
as MY_FIELD2

WHERE IsNumeric(MY_FIELD) = 1 AND CAST(MY_FIELD as VARCHAR(5)) NOT LIKE '%.%'

That is probably the simplest solution. Unless your MY_FIELD contains .00 or something of that sort. In which case, cast it to a float to remove any trailing .00s


declare @i numeric(28,5) = 12.0001


if (@i/cast(@i as int) > 1)
begin
    select 'this is not int'
end
else
begin
    select 'this is int'
end

Necromancing.
As of SQL-Server 2012+, you can use TRY_CAST, which returns NULL if the cast wasn't successful.

Example:

DECLARE @foo varchar(200)
SET @foo = '0123' 
-- SET @foo = '-0123' 
-- SET @foo = '+0123' 
-- SET @foo = '+-0123' 
-- SET @foo = '+-0123' 
-- SET @foo = '.123' 
-- SET @foo = '1.23' 
-- SET @foo = '.' 
-- SET @foo = '..' 
-- SET @foo = '0123e10' 

SELECT CASE WHEN TRY_CAST(@foo AS integer) IS NULL AND @foo IS NOT NULL THEN 0 ELSE 1 END AS isInteger 

This is the only really reliable way.

Should you need support for SQL-Server 2008, then fall back to Sam DeHaan's answer:

SELECT CASE WHEN ISNUMERIC(@foo + '.e0') = 1 THEN 1 ELSE 0 END AS isInteger 

SQL-Server < 2012 (aka 2008R2) will reach end of (extended) support by 2019-07-09.
At this time, which is very soon, support for < 2012 can be dropped.
I wouldn't use any of the other hacks at this point in time anymore.
Just tell your frugal customers to update - it's been over 10 years since 2008.


Sometimes you don't get to design the database, you just have to work with what you are given. In my case it's a database located on a computer that I only have read access to which has been around since 2008.

I need to select from a column in a poorly designed database which is a varchar with numbers 1-100 but sometimes a random string. I used the following to get around it (although I wish I could have re designed the entire database).

SELECT A from TABLE where isnumeric(A)=1

This work around with IsNumeric function will work:

select * from A where ISNUMERIC(x) =1 and X not like '%.%'

or Use

select * from A where x **not like** '%[^0-9]%'


See whether the below query will help

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

I think that there is something wrong with your database design. I think it is a really bad idea to mix varchar and numbers in one column? What is the reason for that?

Of course you can check if there are any chars other than [0-9], but imagine you have a 1m rows in table and your are checking every row. I think it won't work well.

Anyway if you really want to do it I suggest doing it on the client side.


Had the same question. I finally used

where ATTRIBUTE != round(ATTRIBUTE)

and it worked for me


WHERE IsNumeric(value + 'e0') = 1 AND CONVERT(FLOAT, value) BETWEEN -2147483648 AND 2147483647

Use TRY_CONVERT which is an SQL alternative to TryParse in .NET. IsNumeric() isn’t aware that empty strings are counted as (integer)zero, and that some perfectly valid money symbols, by themselves, are not converted to (money)zero. reference

SELECT @MY_VAR = CASE WHEN TRY_CONVERT(INT,MY_FIELD) IS NOT NULL THEN MY_FIELD
                 ELSE 0
                 END
FROM MY_TABLE
WHERE MY_OTHER_FIELD = 'MY_FILTER'

I have a feeling doing it this way is the work of satan, but as an alternative:

How about a TRY - CATCH?

DECLARE @Converted as INT
DECLARE @IsNumeric BIT

BEGIN TRY
    SET @Converted = cast(@ValueToCheck as int)
    SET @IsNumeric=1
END TRY
BEGIN CATCH
    SET @IsNumeric=0
END CATCH

select IIF(@IsNumeric=1,'Integer','Not integer') as IsInteger

This works, though only in SQL Server 2008 and up.


As of SQL Server 2012, the TRY_CONVERT and TRY_CAST functions were implemented. Thise are vast improvements over the ISNUMERIC solution, which can (and does) give false positives (or negatives). For example if you run the below:

SELECT CONVERT(int,V.S)
FROM (VALUES('1'),
            ('900'),
            ('hello'),
            ('12b'),
            ('1.1'),
            ('')) V(S)
WHERE ISNUMERIC(V.S) = 1;

Using TRY_CONVERT (or TRY_CAST) avoids that:

SELECT TRY_CONVERT(int,V.S),
       V.S,
       ISNUMERIC(V.S)
FROM (VALUES('1'),
            ('900'),
            ('hello'),
            ('12b'),
            ('1.1'),
            ('')) V(S)
--WHERE TRY_CONVERT(int,V.S) IS NOT NULL; --To filter to only convertable values

Notice that '1.1' returned NULL, which cause the error before (as a string represtation of a decimal cannot be converted to an int) but also that '' returned 0, even though ISNUMERIC states the value "can't be converted".


Case
When (LNSEQNBR / 16384)%1 = 0 then 1 else 0 end


The following is correct for a WHERE clause; to make a function wrap it in CASE WHEN.

 ISNUMERIC(table.field) > 0 AND PATINDEX('%[^0123456789]%', table.field) = 0

I tried this script and got the answer

ISNUMERIC(Replace(Replace([enter_your_number],'+','A'),'-','A') + '.0e0')

for example for up question this is answer:

SELECT @MY_VAR = CAST(MY_FIELD AS INT)
FROM MY_TABLE
WHERE MY_OTHER_FIELD = 'MY_FILTER' and ISNUMERIC(Replace(Replace(MY_FIELD,'+','A'),'-','A') + '.0e0') = 1

With sqlserver 2005 and later you can use regex-like character classes with LIKE operator. See here.

To check if a string is a non-negative integer (it is a sequence of decimal digits) you can test that it doesn't contain other characters.

SELECT numstr
  FROM table
 WHERE numstr NOT LIKE '%[^0-9]%'

Note1: This will return empty strings too.

Note2: Using LIKE '%[0-9]%' will return any string that contains at least a digit.

See fiddle


Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to tsql

Passing multiple values for same variable in stored procedure Count the Number of Tables in a SQL Server Database Change Date Format(DD/MM/YYYY) in SQL SELECT Statement Stored procedure with default parameters Format number as percent in MS SQL Server EXEC sp_executesql with multiple parameters SQL Server after update trigger How to compare datetime with only date in SQL Server Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot Printing integer variable and string on same line in SQL

Examples related to casting

Subtracting 1 day from a timestamp date Cast object to interface in TypeScript TypeScript enum to object array Casting a number to a string in TypeScript Hive cast string to date dd-MM-yyyy Casting int to bool in C/C++ Swift double to string No function matches the given name and argument types C convert floating point to int PostgreSQL : cast string to date DD/MM/YYYY

Examples related to integer

Python: create dictionary using dict() with integer keys? How to convert datetime to integer in python Can someone explain how to append an element to an array in C programming? How to get the Power of some Integer in Swift language? python "TypeError: 'numpy.float64' object cannot be interpreted as an integer" What's the difference between integer class and numeric class in R PostgreSQL: ERROR: operator does not exist: integer = character varying C++ - how to find the length of an integer Converting binary to decimal integer output Convert floats to ints in Pandas?

Examples related to isnumeric

How can you tell if a value is not numeric in Oracle? T-sql - determine if value is integer PHP is_numeric or preg_match 0-9 validation Identify if a string is a number jQuery: what is the best way to restrict "number"-only input for textboxes? (allow decimal points) How do you test your Request.QueryString[] variables?