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. His udf_IsNatural returns 1 only when the value is a positive integer.
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
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
3 Comments. Leave new
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.