Summary:
The following is a function that Isnumber validates an integer to make a numerical judgment in a SQL script, as follows:
例:实现原理:判断 是否包含特殊字符,是否全部为0-9的自然数组成 CREATE FUNCTION dbo.check_number(/*验证一字符串类型是否为整型数值类型*/@checkTmp VARCHAR(100))RETURNS BITBEGINDECLARE @tmp BITIF LEFT(@checkTmp, 1) = ‘-‘SET @checkTmp = SUBSTRING(@checkTmp, 2, LEN(@checkTmp))IF (PATINDEX(‘%[^0-9-]%‘, @checkTmp) = 0AND CHARINDEX(‘-‘, @checkTmp) <= 1AND @checkTmp NOT IN (‘.‘, ‘-‘, ‘+‘, ‘^‘)AND LEN(@checkTmp)>0AND @checkTmp NOT LIKE ‘%-%‘)SET @tmp = 1ELSESET @tmp = 0RETURN @tmpENDGO/*数据验证*/SELECT ‘878‘ [测试数值],dbo.check_number(‘999‘) [测试返回值];SELECT ‘testData‘ [测试数值],dbo.check_number(‘abc‘) [测试返回值];SELECT ‘8-8‘ [测试数值],dbo.check_number(‘9+9‘) [测试返回值];SELECT ‘8.9‘ [测试数值],dbo.check_number(‘$9.9‘) [测试返回值];SELECT ‘maomao365.com‘ [测试数值],dbo.check_number(‘maomao365.com‘) [测试返回值];godrop function check_number
MSSQL SQL Server verifies integer function sharing