将 varchar 转换为数字类型的算术溢出错误.'10' <= 9.00 [英] Arithmetic overflow error converting varchar to data type numeric. &#39;10&#39; &lt;= 9.00

问题描述

以下是我正在使用的表结构和数据类型的子集.

Below is a subset of the kind of table structure and data i'm working with.

CREATE TABLE #Test
(
     Val varchar(5)
    ,Type varchar(5)
)

INSERT #Test VALUES ('Yes','Text')
INSERT #Test VALUES ('10','Int')
INSERT #Test VALUES ('10.00','Float')
INSERT #Test VALUES ('9.00','Float')
INSERT #Test VALUES ('9','Int')

我想编写一个查询，让我知道列 'Val' 是否 <= 9.00(必须是数字数据类型).我通过执行以下操作来做到这一点:

I want to write a query that will let me know if the column 'Val' is <= 9.00 (must be of numeric data type). I did this by doing the following:

SELECT *
FROM
    (
        SELECT Val
        FROM #Test
        WHERE Type = 'Int'
    ) IntsOnly
WHERE IntsOnly.Val <= 9.00

这给了我一个算术溢出错误.但是，如果我排除值为10"的数据行:

This gives me an arithmetic overflow error. However, if I exclude the row of data with the value '10':

SELECT *
FROM
    (
        SELECT Val
        FROM #Test
        WHERE Type = 'Int'
        AND Val <> '10'
    ) IntsOnly
WHERE IntsOnly.Val <= 9.00

它可以正常工作.我的问题不是如何解决这个问题，因为我知道我可以简单地将数据转换为我需要的格式.

我的问题是为什么Val"列中的10"值返回错误.当然，逻辑应该只返回 'False' 并简单地排除行，因为 '10'(我认为是隐式转换的)大于 9.00.

My question is why the value of '10' in the column 'Val' is returning an error. Surely the logic should just return 'False' and simply exclude the rows because '10' (which I assume is implicitly converted) is greater than 9.00.

谢谢.

推荐答案

这会生成算术溢出，因为它试图将 Val 列隐式转换为 NUMERIC(3,2)，这很自然将溢出 2 位值，如 10.

This generates an Arithmetic Overflow because it is trying to implicitly cast the Val column to a NUMERIC(3,2), which naturally will overflow on a 2-digit value like 10.

它使用 NUMERIC(3,2) 作为目标类型和大小，因为这是 9.00 似乎适合的最小数字.

It's using NUMERIC(3,2) as the target type and size because that is the smallest numeric that 9.00 appears to fit into.

解决方案当然是使用显式 CASTing 而不是隐式执行

The solution, of course, is to use explict CASTing instead of doing it implicitly

这篇关于将 varchar 转换为数字类型的算术溢出错误.'10' <= 9.00的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！