Comprehensive Analysis of Checking if a VARCHAR is a Number in T-SQL: From ISNUMERIC to Regular Expression Approaches

Fundamental Principles and Limitations of ISNUMERIC Function

In T-SQL, the ISNUMERIC function is commonly used to determine if a string represents a number. According to Microsoft's official documentation, this function checks whether an expression can be converted to any numeric type, including integers, decimals, currency, etc. However, this broad definition can lead to false positives in practical applications.

For example, the following strings would all be evaluated as valid numbers by ISNUMERIC:

SELECT ISNUMERIC('123')        -- Returns 1 (true)
SELECT ISNUMERIC('12.34')      -- Returns 1 (true)
SELECT ISNUMERIC('£')     -- Returns 1 (true, pound symbol)
SELECT ISNUMERIC('$100')       -- Returns 1 (true, dollar symbol)
SELECT ISNUMERIC('0d0')        -- Returns 1 (true, scientific notation)

The fundamental reason for this behavior is that ISNUMERIC is designed not merely to detect pure digit strings, but to determine if a string can be converted to any numeric type supported by SQL Server. Therefore, strings containing currency symbols, decimal points, scientific notation, etc., are all accepted.

LIKE Expression Method for Precise Number Detection

When strict detection of strings containing only digits 0-9 is required, T-SQL's LIKE expression with negative pattern matching can be employed. The core logic is to check whether the string contains any non-digit characters.

The basic syntax is as follows:

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

This expression means: if the string does not contain any characters outside the range 0-9, it returns true. The ^ symbol inside the brackets indicates negation, and 0-9 specifies the digit range.

Here is a complete example:

DECLARE @testString VARCHAR(50)
SET @testString = '12345'

SELECT 
    CASE 
        WHEN @testString NOT LIKE '%[^0-9]%' 
        THEN 'Pure digit string'
        ELSE 'Non-pure digit string'
    END AS DetectionResult

For different test cases, this method performs as follows:

• '123' → Returns 'Pure digit string'
• 'abc123' → Returns 'Non-pure digit string'
• '12.34' → Returns 'Non-pure digit string' (because it contains a decimal point)
• '00100' → Returns 'Pure digit string'

Comparison of Both Methods and Selection Recommendations

The main advantage of the ISNUMERIC function lies in its built-in nature and simplicity, making it suitable for scenarios where detecting whether a string can be converted to a numeric type is required. However, when the requirement is explicitly to detect pure digit strings, its broad acceptance criteria may not meet the needs.

The LIKE expression method, while requiring more code, offers precise control. It ensures that the string contains only specified digit characters, excluding all others, including decimal points, symbols, and letters.

In practical applications, the choice between methods depends on specific requirements:

1. If detecting whether a string can be converted to a numeric type (including numbers with signs or decimal points) is needed, use ISNUMERIC
2. If strict detection of strings containing only digits 0-9 is required, use the LIKE expression method
3. For cases requiring handling of leading or trailing spaces, combine with LTRIM(RTRIM()) functions

Here is a practical example combining both approaches:

CREATE FUNCTION dbo.IsPureNumber (@input VARCHAR(100))
RETURNS BIT
AS
BEGIN
    DECLARE @result BIT
    
    IF @input IS NULL
        SET @result = 0
    ELSE IF LEN(@input) = 0
        SET @result = 0
    ELSE IF @input NOT LIKE '%[^0-9]%'
        SET @result = 1
    ELSE
        SET @result = 0
    
    RETURN @result
END

This user-defined function encapsulates the pure number detection logic, providing a reusable solution.