SQL2000 安全地将 VARCHAR(256) 转换为 INT

Question

我在 SQL2000 上将 varchar 安全地转换为 int 时遇到一些问题。

我的问题的第 1 部分是 IsNumeric 返回如果您仅查找整数，则误报。我知道为什么 IsNumeric 会这样做（浮点数、金钱等也是数字），所以我在谷歌上寻找 IsInteger 函数。

我发现以下用户定义函数（UDF）：

CREATE FUNCTION dbo.IsInteger
(  
    @num VARCHAR(64)  
)  
RETURNS BIT  
BEGIN  
    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

这似乎在检查整数方面做得很好：

declare @num varchar(256);
declare @num2 varchar(256);
set @num = '22312311';
set @num2 = '22312311.0';
SELECT  @num AS [character], 
 dbo.IsInteger(@num) AS [isInteger], 
 CASE dbo.IsInteger(@num)WHEN 1 THEN convert(int, @num) ELSE NULL END AS [integer]
UNION
SELECT  @num2 AS [character], 
 dbo.IsInteger(@num2) AS [isInteger], 
 CASE dbo.IsInteger(@num2)WHEN 1 THEN convert(int, @num2) ELSE NULL END AS [integer];

但是它不会验证整数是否在范围内（-2^31 <=> 2^31 - 1)

declare @num varchar(256);
set @num = '2147483648';
SELECT  @num AS [character], 
 dbo.IsInteger(@num) AS [isInteger], 
 CASE dbo.IsInteger(@num)WHEN 1 THEN convert(int, @num) ELSE NULL END AS [integer];

抛出

服务器：Msg 248，Level 16，State 1，Line 3
nvarchar 值“2147483648”的转换溢出了 int 列。超出最大整数值。

SQL2000 没有 TRY/ CATCH （答案假定 ISNUMERIC() 返回不误报）并且转换错误会导致整个批次失败，即使在 UDF 内，根据 此网站：

当UDF发生错误时， 函数的执行被中止 立即，查询也是如此，并且 除非错误是导致中止的错误 批处理，继续执行 下一条语句 – 但 @@error 是 0！

即使他们没有这样做，仍然会掩盖@@error。我也无法转换为 bigint，因为它仍然可能崩溃（尽管不那么频繁），并且此查询是 UNION 的一部分，该 UNION 输出到 XML，并由 VB6 使用 XSLT 进行进一步验证和转换COM DLL 并显示在 2001 年编码的网站上，所以我真的（不真的）不想更改查询输出！

因此，这让我陷入了这个看似简单的任务：

如果 varchar 可转换为 int 则转换为 int 否则给我 NULL

任何指针/解决方案都会非常感激，但请注意，我不能，在没有根据情况，更改源列的数据类型，也不更改输入数据时的验证。

Answer 1

编辑：

SQL Server 中的数字不能超过 decimal(38,0)< /a> (+/- 10^38 -1) 因此无法捕获或转换它们。这意味着长度可以为 37 个字符，并且 CAST 为十进制(38,0)

SELECT
    CASE
        WHEN CAST(MyColumn AS decimal(38,0) BETWEEN -2147483648 AND 2147483647 THEN  CAST(MyColumn AS int)
        ELSE NULL
    END
FROM
    MyTable
WHERE
    ISNUMERIC(MyColumn + '.0e0') = 1 AND LEN(MyColumn) <= 37

尊重 本文介绍了 .0e0 技巧

编辑OP
这个问题引导我了解以下更新的 IsInteger 函数。

CREATE FUNCTION dbo.IsInteger
(  
    @num VARCHAR(256)  
)  
RETURNS BIT
BEGIN    
    RETURN CASE 
            WHEN ISNUMERIC(@num + '.0e0') = 1  AND convert(decimal(38,0), @num) BETWEEN -2147483648 AND 2147483647 THEN  1
            ELSE 0
    END
END

Answer 2

您可以在该函数中添加更多检查：

CREATE FUNCTION [dbo].[IsInteger] 
(   
    @num VARCHAR(64)   
)   
RETURNS BIT   
BEGIN   
    IF LEFT(@num, 1) = '-'   
        SET @num = SUBSTRING(@num, 2, LEN(@num))   

    DECLARE @IsInt BIT

    SELECT @IsInt = 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   

    IF @IsInt = 1
        BEGIN

            IF LEN(@num) <= 11
                BEGIN
                    DECLARE @test bigint
                    SELECT @test = convert(bigint, @num)
                    IF @test <= 2147483647 AND @test >= -2147483648
                        BEGIN
                            set @IsInt = 1
                        END
                    ELSE
                        BEGIN
                            set @IsInt = 0
                        END
                END
            ELSE
                BEGIN
                    set @IsInt = 0
                END
        END


    RETURN @IsInt

END 

我没有机会测试，但我认为它应该有效 - 我已将其尽可能详细地保留