tSQL - 从varchar到numeric的转换适用于除整数之外的所有内容

I have a table with numbers in a varchar(255) field. They're all greater than one and have multiple decimal places. I'd like to convert them to integers. According to every web site I've consulted, including this one on *, either of these should work:

我在varchar（255）字段中有一个带有数字的表。它们都大于一个并且有多个小数位。我想将它们转换为整数。根据我咨询过的每个网站，包括*上的这个网站，其中任何一个都可以工作：

SELECT CAST(VarcharCol AS INT) FROM MyTable

SELECT CONVERT(INT, VarcharCol) FROM MyTable

These both work for me for every kind of numeric value but integer - I can convert to float, decimal, etc. just fine, but trying to convert to integer gives me the following error:

这些都适用于我的各种数值但整数 - 我可以转换为浮点数，十进制等等就好了，但试图转换为整数给我以下错误：

Conversion failed when converting the varchar value '7082.7758172' 
to data type int.

I've worked around the problem by converting to data type Decimal(6,0), which works fine. But just for my education, can anyone tell me why converting to data type int (or integer) gives me an error? Thanks.

我通过转换为数据类型Decimal（6,0）解决了这个问题，这很好。但只是为了我的教育，谁能告诉我为什么转换为数据类型int（或整数）给我一个错误？谢谢。

5 个解决方案

#1

Converting a VARCHAR value into an INT fails when there are digits to the right of the decimal to prevent loss of data.

当小数点右边有数字时，将VARCHAR值转换为INT会失败，以防止丢失数据。

If you convert to FLOAT first, then convert to INT, the conversion works.

如果先转换为FLOAT，然后转换为INT，则转换有效。

SELECT CAST(CAST('7082.7758172' as float) as int)

returns

回报

#2

Actually whether there are digits or not is irrelevant. The . (dot) is forbidden if you want to cast to int. Dot can't - logically - be part of Integer definition, so even:

实际上是否有数字是无关紧要的。这个。如果要转换为int，则禁止（点）。 Dot不能 - 逻辑上 - 是Integer定义的一部分，所以即使：

select cast ('7.0' as int)
select cast ('7.' as int)

will fail but both are fine for floats.

会失败，但两者都适合花车。

#3

Presumably, you want to convert values before the decimal place to an integer. If so, use case and check for the right format:

据推测，您希望将小数位前的值转换为整数。如果是这样，用例并检查正确的格式：

SELECT (case when varcharcol not like '%.%' then cast(varcharcol as int)
             else cast(left(varcharcol, chardindex('.', varcharcol) - 1) as int)
        end) IntVal
FROM MyTable;

#4

Try this

尝试这个

declare @v varchar(20)
set @v = 'Number'
select case when isnumeric(@v) = 1 then @v
else @v end

and

和

declare @v varchar(20)
set @v = '7082.7758172'
select case when isnumeric(@v) = 1 then @v
else convert(numeric(18,0),@v) end

#5

-1

SELECT
  convert(numeric(18,5),Col1), Col2
     FROM DBname.dbo.TableName
         WHERE isnumeric(isnull(Col1,1)) <> 0

#1