SQL SERVER – UDF – Validate Integer Function

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 ($).

Solarwinds

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 (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQL SERVER – 2005 – Find Stored Procedure Create Date and Modified Date
Next Post
SQL SERVER – Fix : ERROR : Msg 1033, Level 15, State 1 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Related Posts

8 Comments. Leave new

  • Gents,

    thanks for this script , i were searching for script like this

    thanks

    Reply
  • Hi,

    I test value ‘1a’ and function return 1; (error)

    Thanks

    Reply
  • Hi Pinalkumar

    I just tested on 2 different instances and cannot confirm your results. NumericTest always return 1. Something seems wrong here.

    SQL Server 2005, my @@VERSION:

    Microsoft SQL Server 2005 – 9.00.3068.00 (Intel X86) Feb 26 2008 18:15:01
    Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    Microsoft SQL Server 2005 – 9.00.4035.00 (Intel X86) Nov 24 2008 13:01:59
    Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 6.0 (Build 6001: Service Pack 1)

    Reply
  • Another one

    Madhivanan

    Reply
  • Hi Pinal,
    Thank you for the UDF,I have a similar requirement for validating for numbers .my requirement is to check a varchar has got only numbers ,we are validating for telephone column and we are not strictly to be a valid telephone number,all we require is a number not any other charecters or wild charecters,is there any function to do this.I have tried regular expressions but no luck and performence is very bad.

    Many Thanks
    Chandu

    Reply
  • Hi pinal after running your script i noticed one error it should only return 1 for character as well as numeric field kindly ceck it

    Reply
  • I really love your website.. Excellent colors & theme.
    Did you create this amazing site yourself? Please reply back as I’m trying to create my own personal website and would love to know where you got this from or what the theme is called.

    Cheers!

    Reply
  • What is returned if you pass Null or a 0 length string? Looks like it would fail? And what about 9.9? Is that a number?

    Reply

Leave a Reply

Menu