MS SQL自定义函数IsPositiveInteger

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

 

MS SQL自定义函数IsPositiveInteger

标签：positive   nbsp   举例   UNC   turn   star   break   img   one