Journey to SQL Authority with Pinal Dave

I received quite a good feedback about my post about SQL SERVER - Validate Field For DATE datatype using function ISDATE()

One of the most interesting comment I received from my reader from Canada. I was suggested just like ISDATE() to write about ISNUMERIC() which can be used to validate numeric values.

As per BOL:
ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0. ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($).

Now this is issue with ISNUMERIC() function. It even suggest variable as numeric when it is not. I use following function to validate instead of ISNUMERIC() to validate if number is numeric or not.

Code:

CREATE FUNCTION dbo.udf_IsNumeric
    (
        @Number VARCHAR(100)
    )
RETURNS BIT
    BEGIN
    DECLARE @Ret BIT
    IF LEFT(@Number, 1) = '-'
        SET @Number = SUBSTRING(@Number, 2, LEN(@Number))
        IF (PATINDEX('%[^0-9-]%’, @Number) = 0
AND CHARINDEX(’-', @Number) <= 1
                AND @Number NOT IN ('.', '-', '+', '^')
                AND LEN(@Number)>0
                AND @Number NOT LIKE '%-%')
        SET @Ret = 1
        ELSE
        SET @Ret = 0
    RETURN @Ret
    END
GO

Example:

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

ResultSet:

TestValue NumericTest
--------- -----------
999       1

TestValue NumericTest
--------- -----------
abc       0

TestValue NumericTest
--------- -----------
9+9       0

TestValue NumericTest
--------- -----------
$9.9      0

TestValue    NumericTest
------------ -----------
SQLAuthority 0

This function is part of my personal script library and source of this function was not listed with it. I searched online and found similar function online here. There is in-depth analysis of ISNUMERIC() function and its test. I encourage my readers to read that article as well.

Reference : Pinal Dave (http://www.SQLAuthority.com)