T-sql - determine if value is integer

Question

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

Solution 1

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.

Solution 2

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.

Solution 3

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

Solution 4

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

Solution 5

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.

View more solutions

Comments

[Orlando Colamatteo]

The function shown in the blog post is flawed in several ways. DO NOT USE IT!!! These inputs return a positive result which is clearly incorrect: SELECT /*more than 18 characters-function should accept MAX data type and test more for invalid length*/ dbo.IsInt32('99999999999999999999999999999999999999999999930‌​'), /* 18 characters, but clearly not a valid int */ dbo.IsInt32('999999999999999999');

[Iain Samuel McLean Elder]

The pure T-SQL function fails with a "data truncation" error if the input is longer than 38 characters. See my SQLFiddle where I compare this solution and the currently accepted answer.

[Douglas]

I've attempted to come up with a solution that should address all border cases: stackoverflow.com/a/24250511/1149773

[Marc]

ISNUMERIC(',') returns 1 (true) which IMO is false!

[Ben Fransen]

While I encounter troubly databases quite often I'd like to say that poorly designed databases are not always the fault of developers. Budget is your true enemy in most cases.

[Jerry]

Agreed that this could be an issue for really fuky, long edge cases. But works great for basic integer (i.e. numbers between 1 and 1000) testing. You can easily add a length check to the string in this case.

[Tomas Pastircak]

because SELECT ROUND('abc',0) throws an exception.

[DatumPoint]

...assuming you only want to return positive integers.

[1010]

@DatumPoint You're right, it works for positive integers or 0.

[Elaskanator]

This does not work for comma-delimited values. For example, ISNUMERIC('123,456,789') returns 1

[Hoppeduppeanut]

This has already been covered in a previous answer.

[Tab Alleman]

For me this results in an error when fieldname contains a non-numeric string like 'Test'.

[Reversed Engineer]

@Joshua - SELECT ISNUMERIC (CHAR(11) + '.e0') returns 0 in my case, thereby passing the test, not failing it

[Roeland]

Hi Grady, what is LNSEQNBR in T-SQL? Ans whats with the number 16384?

[AcePL]

This is the solution I used. It has the added benefit of being the textbook definition of what an integer is. I would mark this one as an answer to the OP question.

[Lee Chetwynd]

Sometimes you get what you are given to work with, and what you are given to work with may be a poor database design.

[F. Müller]

Hi, the question is over 8 years old and has already an accepted answer. This should be a comment rather than an answer. I don't see how rounding would solve the issue addressed in the question.

[Tim Gautier]

I don't believe the question implies a need to determine if a string is an int.