MS SQL Server 的 isnumeric 函数返回为1的字符串有时候并不能被直接 CAST 成数值型字段。比如包含逗号(12,345)、制表符的字符串用isnumeric来判断的话返回都是1。所以在进行某些数据转化的时候想靠 isnumeric 来保证转化脚本不报数据类型转换失败的错误是不保险的。
解决办法只有用额外的字符串校验来做判断:
DROP FUNCTION dbo.isReallyNumeric;
GO
CREATE
GO
IF OBJECT_ID(N'dbo.isReallyInteger', N'FN') IS NOT NULL
DROP FUNCTION dbo.isReallyInteger;
GO
CREATE FUNCTION dbo.isReallyInteger
(
@num VARCHAR(128)
)
RETURNS BIT
BEGIN
set @num = LTRIM(RTRIM(@num))
IF LEFT(@num, 1) = '-'
SET @num = SUBSTRING(@num, 2, LEN(@num))
RETURN CASE
WHEN PATINDEX('%[^0-9-]%', @num) = 0
AND CHARINDEX('-', @num) <= 1
AND @num NOT IN ('.', '-', '+', '^')
AND LEN(@num)>0
AND @num NOT LIKE '%-%'
AND Isnumeric(@num)>0 --这里再次判断一下。因为如果符号为ⅠⅡ这样的特殊字符就不行。
THEN
1
ELSE
0
END
END
GO
通过创建以上的自定义函数来检查字符串是否确实可以被CAST到数值型字段来保存,替换掉调用 isnumeric 的地方即可(因为带前后导空格的字符串可以被自动CAST,所以先做TRIM处理)。