SQL SERVER – Validating Positive Integer Strings with Explicit Boundaries

To Validate Natural integer strings, I define a nonempty positive ASCII-digit contract. A customer wanted a simpler validator.

-- Function definition for an approved target database, SQL Server 2016 SP1+.
CREATE OR ALTER FUNCTION dbo.udf_IsPositiveNaturalNumber (@Number varchar(100))
RETURNS bit
AS
BEGIN
    RETURN CASE WHEN DATALENGTH(@Number) > 0
        AND @Number COLLATE Latin1_General_100_BIN2 NOT LIKE '%[^0-9]%'
        AND @Number COLLATE Latin1_General_100_BIN2 LIKE '%[1-9]%'
        THEN 1 ELSE 0 END;
END;
GO
SELECT v.TestValue, dbo.udf_IsPositiveNaturalNumber(v.TestValue) AS IsPositiveNatural
FROM (VALUES ('999'),('-999'),('abc'),('9+9'),('$9.9'),('SQLAuthority'),
             ('0'),('000'),('001'),(''),('9 '),(CAST(NULL AS varchar(100)))) v(TestValue);

My original digit-only test accepted zero and an empty string. This version requires at least one digit from 1 through 9. It rejects signs, punctuation, spaces and NULL, while permitting leading zeros. Use TRY_CONVERT when the target integer range also matters.

Binary collation makes the ASCII rule explicit. The varchar(100) parameter defines a length contract. Check longer inputs before conversion or truncation. CREATE OR ALTER requires a supported engine and permission; the companion checks the expression without creating a function.

Related reading

• Comprehensive Database Performance Health Check
• SQL SERVER – UDF – Validate Natural Integer Function
• 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

A valid digit representation is not proof of integer range, it is text that meets an explicit input contract.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.