SQL SERVER – UDF – Validate Positive Integer Function – Validate Natural Integer Function

Few days ago I wrote SQL SERVER – UDF – Validate Integer Function. It was very interesting to write this and developers at my company started to use it. One Jr. DBA modified this function to validate only positive integers. I will share this with everybody who are interested in similar functionality.

Code:
CREATE FUNCTION [dbo].[udf_IsNatural]
(
@Number VARCHAR(100)
)
RETURNS BIT
BEGIN
DECLARE
@Ret BIT
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 '-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
——— ———–
-999 0

TestValue NumericTest
——— ———–
abc 0

TestValue NumericTest
——— ———–
9+9 0

TestValue NumericTest
——— ———–
$9.9 0

TestValue NumericTest
———— ———–
SQLAuthority 0

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

About these ads

3 thoughts on “SQL SERVER – UDF – Validate Positive Integer Function – Validate Natural Integer Function

  1. I assume the name of the function is left over from a previous example, but the naming of it does not tell me what it actually does.
    It should be named something like dbo.udf_IsPositiveIntegerOnly

    The name udf_IsNumeric would mean to me that it will evaluate input and let me know if it’s a number or not.
    9.9 is a number, and -999 is a number, but neither of these are positive integers.

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

  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #047 | 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