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

source code

 

列举例子说明：

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

source code