SQL Server不靠谱的isnumeric函数【转】

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))

        )

        AND Isnumeric(@num)>0 --这里再次判断一下。因为如果符号为ⅠⅡ这样的特殊字符就不行。

    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 '%-%'
        AND Isnumeric(@num)>0 --这里再次判断一下。因为如果符号为ⅠⅡ这样的特殊字符就不行。    

    THEN
        1
    ELSE
        0
    END
END
GO

 

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