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