MS SQL自定义函数IsNumeric

判断字符串是否为纯数字，负数不算。如’00012′,’54585′,’1000′

 

set ansi_nulls on
go
set quoted_identifier on
go
create function [dbo].[svf_isnumeric] 
(
  @string nvarchar(max)
) returns bit  --函数返bit数据类型，是数字返回1，非数字返回0。
as
begin
    declare @rtv bit = 1   
    declare @str nvarchar(max) = ltrim(rtrim(isnull(@string,''))) --去除前后空格，如果为null转为''
    
    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
    
    return @rtv
end

source code

 

创建一个例子来演示：

 

create table [dbo].[utable] ([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].[utable] ([col1],[col2],[col3],[col4],[col5],[col6],[col7]) 
values ('0.455','000435','sf46dg','4000','$%9kj','-0034','-8554')
go


select [dbo].[svf_isnumeric] ([col1]) as [col1],
[dbo].[svf_isnumeric] ([col2]) as [col2],
[dbo].[svf_isnumeric] ([col3]) as [col3],
[dbo].[svf_isnumeric] ([col4]) as [col4],
[dbo].[svf_isnumeric] ([col5]) as [col5],
[dbo].[svf_isnumeric] ([col6]) as [col6],
[dbo].[svf_isnumeric] ([col7]) as [col7]
from [dbo].[utable] 
go

source code