SQL错误 - varchar转numeric

Question

我将一个扁平文件导入到了SQL数据库中，导致所有字段都被创建为varchar(50)类型。我成功地更改了大多数字段的数据类型，但在重量字段上遇到了错误。重量字段中的所有数据总长度都小于6个字符，且只包含整数或小数。我尝试过以下两种方法：

UPDATE MAWB
SET Weight = CAST(Weight AS decimal(12,2))

并且：

ALTER TABLE MAWB
ALTER COLUMN [Weight] decimal(10,2)

我经常遇到这个错误：将varchar类型的数据转换为数值型时出错。

我已经检查过所有字段，都被认为是数值型：

SELECT COUNT(*)
FROM MAWB
WHERE ISNUMERIC(Weight) = 0

我还尝试了LTRIM和RTRIM，只是为了安全起见，但是当我尝试更改字段时仍然会出现错误。有人知道还有什么可能导致这个错误吗？

- Michael

Answer 1

因此，ISNUMERIC()远非完美。它可以返回诸如e、$、'€'和tab等许多意料之外的值。但当然你不能将它们转换为每种数值类型（ISNUMERIC()仅标识它可以转换为至少一种数值类型）。LTRIM()和RTRIM()无法帮助，因为它们无法清除tab和其他类似字符。

要识别真正不是数字的行：

SELECT weight FROM dbo.table WHERE column NOT LIKE '%[^0-9.-]%';

或者您可以编写一个更详细的标量函数，就像上面链接中的函数一样，它可以处理更多的边缘情况:

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

    DECLARE @pos TINYINT  

    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 

一旦您确定了这些行并修复了数据，请务必修复表格。

- Aaron Bertrand

Answer 2

您可以使用以下查询找到有问题的行：

SELECT *
FROM MAWB
WHERE TRY_CONVERT(decimal(12,2),Weight) IS NULL
AND Weight IS NOT NULL

- Steve Kass

---

很好的发现，我错过了2012标签。 - Aaron Bertrand

这很有帮助。发现所有有问题的字段都超过了1,000，因此有逗号。进行了替换以删除它们，然后转换没有问题。谢谢。 - Michael