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

Answer 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.

Answer 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.