MS SQL自定義函數IsPositiveInteger

判斷字元串是否為正整數，0開始的的數字不算。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[svf_IsPositiveInteger] 
(
  @string NVARCHAR(MAX)
) RETURNS BIT  --函數返BIT數據類型，是數字返回1，非數字返回0。
AS
BEGIN
    DECLARE @rtv BIT = 1   
    DECLARE @str NVARCHAR(MAX) = LTRIM(RTRIM(ISNULL(@string,'')))  --去除前後空格，如果為NULL轉為''

    IF ASCII(SUBSTRING(@str, 1, 1)) = 48 --如果字元串第一位為0
    BEGIN
        SET @rtv = 0 --直接判斷為非正整數
    END
    ELSE
    BEGIN
        DECLARE @start INT = 1; 
        DECLARE @end INT = LEN(@str) --獲取字元串長度
     
        WHILE (@start <= @end)  --迴圈字元串每一個字元
        BEGIN
            DECLARE @Numeric VARCHAR(1) = ''
    
            SET @Numeric = SUBSTRING(@str, @start, @start + 1) -- 每迴圈一次從左邊獲取一位字元
 
            IF ASCII(@Numeric) >= 48 AND ASCII(@Numeric) <= 57 --如果是數字
            BEGIN
                SET @start = @start + 1;
                CONTINUE --繼續迴圈             
            END
            ELSE
            BEGIN
                SET @rtv = 0
                BREAK  --跳出迴圈
            END
        END
    END
    RETURN @rtv
END

列舉例子說明：

CREATE TABLE [dbo].[uTstTable] ([col1] NVARCHAR(20),[col2] NVARCHAR(20),[col3] NVARCHAR(20),[col4] NVARCHAR(20),[col5] NVARCHAR(20),[col6] NVARCHAR(20),[col7] NVARCHAR(20))
GO

INSERT INTO [dbo].[uTstTable] ([col1],[col2],[col3],[col4],[col5],[col6],[col7]) 
VALUES ('0.65','000435','SF46DG','3800','$54KQ','-0034','-855.4')
GO


SELECT [dbo].[svf_IsPositiveInteger] ([col1]) AS [col1],
[dbo].[svf_IsPositiveInteger] ([col2]) AS [col2],
[dbo].[svf_IsPositiveInteger] ([col3]) AS [col3],
[dbo].[svf_IsPositiveInteger] ([col4]) AS [col4],
[dbo].[svf_IsPositiveInteger] ([col5]) AS [col5],
[dbo].[svf_IsPositiveInteger] ([col6]) AS [col6],
[dbo].[svf_IsPositiveInteger] ([col7]) AS [col7]
FROM [dbo].[uTstTable] 
GO