Effective Methods for Determining Integer Values in T-SQL

Problem Background and Challenges

In SQL Server development, there is often a need to determine whether a value is a valid integer type. Many developers initially consider using the built-in ISNUMERIC function, but this function has significant limitations. ISNUMERIC not only returns integers as valid numbers but also identifies values containing decimal points (e.g., 123.45), values represented in scientific notation (e.g., 1.23e5), and other numeric formats as valid numbers. This broad identification range cannot meet the requirement for precise integer determination.

Consider the following typical scenario: when attempting to convert a field value to an integer, if the value is not actually an integer, directly using CAST or CONVERT functions will cause a runtime error. For example:

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

If MY_FIELD contains non-integer values, this query will fail. Therefore, precise integer validation before conversion is crucial.

Core Solution Analysis

Intelligent Method Based on String Manipulation

The solution proposed in Answer 1 employs a clever string processing technique. The core idea of this method is to append the '.e0' suffix to the original value and then use the ISNUMERIC function for validation. The sophistication of this method lies in:

• For values that already contain a decimal point (e.g., 123.45), adding '.e0' results in 123.45.e0, creating two consecutive decimal points, causing ISNUMERIC to return 0 (invalid)
• For values represented in scientific notation (e.g., 1.23e5), adding the suffix results in 1.23e5.e0, where two e characters appear, similarly causing ISNUMERIC to判定 as invalid
• Only pure integers remain identifiable as valid numbers after adding '.e0'

Based on this principle, a user-defined function can be created to implement integer validation:

CREATE FUNCTION dbo.IsInteger(@value VARCHAR(50))
RETURNS BIT
AS
BEGIN
    RETURN CASE WHEN ISNUMERIC(@value + '.e0') = 1 THEN 1 ELSE 0 END
END

Regular Pattern Matching Method

Answer 3 provides a pattern matching solution based on the LIKE operator. This method utilizes SQL Server's regex-like character class functionality to determine if a string is an integer by checking if it contains only digit characters:

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

This query uses the [^0-9] pattern to match any non-digit character. NOT LIKE '%[^0-9]%' indicates that the string does not contain any non-digit characters, meaning the string consists entirely of digits.

It is important to note that this method has some limitations:

• Identifies empty strings as valid integers
• Cannot handle signed integers (positive/negative signs)
• Does not verify whether the numeric range is within the valid integer range

Advanced Solution Comparison

Comprehensive Pure T-SQL Solution

The pure T-SQL solution proposed by Itzik Ben-Gan in Answer 2 combines multiple validation techniques to provide more comprehensive integer detection:

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 step-by-step logic of this solution is as follows:

1. First, use ISNUMERIC for basic numeric validation
2. Exclude strings containing illegal characters via the LIKE '%[^-+ 0-9]%' pattern
3. Finally, verify that the converted value is within the valid 32-bit integer range (-2147483648 to 2147483647)

High-Performance CLR Integration Solution

For scenarios with high performance requirements, Answer 2 also provides a solution based on CLR (Common Language Runtime) integration. This method leverages the .NET framework's Int32.TryParse method to create a custom function in SQL Server:

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);
        }
    }
};

Calling this function in T-SQL is very straightforward:

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

Performance Analysis and Selection Recommendations

According to performance test data from Answer 2, the CLR solution took only 2 seconds in a million-row data test, while the pure T-SQL solution required 7 seconds. This significant performance difference primarily stems from:

• CLR functions directly call highly optimized .NET parsing algorithms
• The pure T-SQL solution involves multiple function calls and type conversions
• CLR has inherent advantages in complex string processing

Selection recommendations:

• If project constraints allow only pure T-SQL, the comprehensive CASE WHEN solution is recommended
• If pursuing optimal performance and the environment supports CLR integration, the CLR solution is clearly the better choice
• For simple integer validation scenarios, the string manipulation solution (adding '.e0') provides a good balance

Practical Application Considerations

In the discussion from the reference article, experts emphasized several important considerations:

• Avoid using modulus operations (%) for integer determination in floating-point scenarios
• For floating-point division, the ROUND function with truncation options can be used for integer validation
• The ISNUMERIC function was not designed for precise integer validation and must be used with extreme caution

Here is an example for handling integer validation with floating-point numbers:

DECLARE @a FLOAT, @b FLOAT
SET @a = 6
SET @b = 3
SELECT @a/@b AS [a/b], ROUND(@a/@b, 0) AS [Round(A/B)],
CASE WHEN @a/@b = ROUND(@a/@b, 0) 
     THEN 'Expression is a whole number.' 
     ELSE 'Expression is a fraction.' END AS [result]

Conclusion

Determining whether a value is an integer is a common but complex problem in SQL Server development. The various methods introduced in this article each have their advantages and disadvantages: the string manipulation solution is simple and clever, the regular matching solution is intuitive and easy to understand, the pure T-SQL comprehensive solution is feature-rich, and the CLR integration solution offers卓越 performance. Developers should choose the most appropriate solution based on specific project requirements, performance needs, and environmental constraints. Regardless of the chosen method, understanding its principles and limitations is key to ensuring code correctness.