SQL Server不靠谱的isnumeric函数

原文链接：http://www.zeali.net/entry/638

MS SQL Server 的 isnumeric 函数返回为1的字符串有时候并不能被直接 CAST 成数值型字段。比如包含逗号（12,345）、制表符的字符串用isnumeric来判断的话返回都是1。所以在进行某些数据转化的时候想靠 isnumeric 来保证转化脚本不报数据类型转换失败的错误是不保险的。

解决办法只有用额外的字符串校验来做判断：

IF OBJECT_ID(N'dbo.isReallyNumeric', N'FN') IS NOT NULL
DROP FUNCTION dbo.isReallyNumeric;
GO

CREATE FUNCTION dbo.isReallyNumeric
(
    @num VARCHAR(128)
)
RETURNS BIT
BEGIN
    set @num = LTRIM(RTRIM(@num))
    IF LEFT(@num, 1) = '-'
        SET @num = SUBSTRING(@num, 2, LEN(@num))

    DECLARE @pos INTEGER

    SET @pos = 1 + LEN(@num) - CHARINDEX('.', REVERSE(@num))

    RETURN CASE
    WHEN PATINDEX('%[^0-9.-]%', @num) = 0
        AND @num NOT IN ('.', '-', '+', '^')
        AND LEN(@num)>0
        AND @num NOT LIKE '%-%'
        AND
        (
            ((@pos = LEN(@num)+1)
            OR @pos = CHARINDEX('.', @num))
        )
    THEN
        1
    ELSE
    0
    END
END
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 '%-%'
    THEN
        1
    ELSE
        0
    END
END
GO

通过创建以上的自定义函数来检查字符串是否确实可以被CAST到数值型字段来保存，替换掉调用 isnumeric 的地方即可（因为带前后导空格的字符串可以被自动CAST，所以先做TRIM处理）。

 

还有一个简便的写法是：

PATINDEX('%[^0-9]%', 列名)

如果返回值等于0，则是纯数字型（没有0~9之外的字符）。