SQL SERVER – UDF – Validate Natural Integer Function – Simple Version

Working with SQL Server has been always an interesting experience and every single day, I learn something new. Recently one of my clients of Comprehensive Database Performance Health Check, asked me if I have a script for validating natural integer. I have already blogged about validating positive integer numbers in this post (SQL SERVER – UDF – Validate Natural Integer Function) and today we are going to see the simple version of the same script.

Validate Natural Integer Function

Let us start by creating the following function:

CREATE FUNCTION [dbo].[udf_IsPositiveNaturalNumber]
(
@Number VARCHAR(100)
)
RETURNS BIT
BEGIN
DECLARE @Ret BIT
SET @Ret =CASE WHEN @number NOT LIKE '%[^0-9]%' THEN 1 ELSE 0 END
RETURN @Ret
END
GO

The above function accepts any value and returns 1 if it is a natural positive number otherwise it returns 0. The logic is to check if the given string contains only numbers and no other characters.

Test the above function with the following examples

SELECT '999' TestValue,dbo.[udf_IsPositiveNaturalNumber]('999') IsPositiveNatural;
SELECT '-999' TestValue,dbo.[udf_IsPositiveNaturalNumber]('-999') IsPositiveNatural;
SELECT 'abc' TestValue,dbo.[udf_IsPositiveNaturalNumber]('abc') IsPositiveNatural;
SELECT '9+9' TestValue,dbo.[udf_IsPositiveNaturalNumber]('9+9') IsPositiveNatural;
SELECT '$9.9' TestValue,dbo.[udf_IsPositiveNaturalNumber]('$9.9') IsPositiveNatural;
SELECT 'SQLAuthority' TestValue,dbo.[udf_IsPositiveNaturalNumber]('SQLAuthority') IsPositiveNatural;

The result sets are as shown below

As you can see that the function validates any string passed to this one and tell us if the number is a natural integer or not. You can for sure use the earlier script as well. However, I nowadays use this updated script for the same.

Here are six-part blog post series I have written based on my last 10 years of experience helping with the Comprehensive Database Performance Health Check. I strongly recommend you to read them as they walk you through my business model.

• Consulting 101 – Why Do I Never Take Control of Computers Remotely?
• Consulting 102 – Why Do I Give 100% Guarantee of My Services?
• Consulting 103 – Why Do I Assure SQL Server Performance Optimization in 4 Hours?
• Consulting 104 – Why Do I Give All of the Performance-Tuning Scripts to My Customers?
• Consulting 105 – Why Don’t I Want My Customers to Return Because of the Same Problem?
• Consulting Wrap Up – What Next and How to Get Started

Reference: Pinal Dave (https://blog.sqlauthority.com)