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

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)

About these ads

12 thoughts on “SQL SERVER – UDF – Validate Integer Function

  1. Pingback: SQL SERVER - UDF - Validate Positive Integer Function - Validate Natural Integer Function Journey to SQL Authority with Pinal Dave

  2. 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)

  3. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  4. 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

  5. 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

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #042 | Journey to SQL Authority with Pinal Dave

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #047 | Journey to SQL Authority with Pinal Dave

  8. 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!

  9. Pingback: MySQL – UDF – Validate Integer Function | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s