Published on

July 17, 2014

Understanding ISNUMERIC Function in SQL Server

When working with SQL Server, it is often necessary to determine whether a given string is numeric or not. SQL Server provides a default function called ISNUMERIC for this purpose. However, it is important to note that ISNUMERIC returns 1 for non-numerical values as well, which can lead to unexpected results.

To overcome this limitation, you can create a user-defined function that accurately determines if a string is numeric or not. Let’s take a look at an example:

CREATE FUNCTION udf_IsNumeric (number_in VARCHAR(100)) RETURNS BIT
BEGIN
    DECLARE Ret BIT;
    
    IF number_in NOT REGEXP '[^0123456789]' THEN
        SET Ret := 1;
    ELSE
        SET Ret := 0;
    END IF;
    
    RETURN Ret;
END

In the above function, we use a regular expression to check if the string contains at least one character that is not a digit. By negating this condition with “NOT REGEXP”, we can determine if the string consists of only digits.

Let’s test this function with some examples:

SELECT '999' AS TestValue, udf_IsNumeric('999') AS NumericTest;
SELECT 'abc' AS TestValue, udf_IsNumeric('abc') AS NumericTest;
SELECT '9+9' AS TestValue, udf_IsNumeric('9+9') AS NumericTest;
SELECT '$9.9' AS TestValue, udf_IsNumeric('$9.9') AS NumericTest;
SELECT 'SQLAuthority' AS TestValue, udf_IsNumeric('SQLAuthority') AS NumericTest;

The result of the above queries will be:

TestValueNumericTest
9991
abc0
9+90
$9.90
SQLAuthority0

As you can see, the function accurately determines whether a string is numeric or not, providing more reliable results compared to the default ISNUMERIC function in SQL Server.

Regular expressions can be a powerful tool in SQL Server and can be used in various scenarios. If you want to learn more about regular expressions in MySQL, you can find additional examples here.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.