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://blog.SQLAuthority.com)