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.

SQL SERVER - UDF - Validate Natural Integer Function - Simple Version validatenatural1-800x179

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

SQL SERVER - UDF - Validate Natural Integer Function - Simple Version validatenatural

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.

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

, , ,
Previous Post
SQL SERVER – List Database Available for Current User – HAS_DBACCESS
Next Post
SQL SERVER – Fix Error: Currently This Report Does Not Have Any Data to Show, Because Default Trace Does Not Contain Relevant Information

Related Posts

Leave a Reply

Menu