CAST 和 IsNumeric [英] CAST and IsNumeric

问题描述

为什么以下查询会返回将数据类型 varchar 转换为 bigint 时出错"?IsNumeric 不会使 CAST 安全吗?我已经尝试了演员表中的所有数字数据类型，并得到相同的错误转换..."错误.我认为结果数字的大小不是问题，因为溢出是一个不同的错误.

Why would the following query return "Error converting data type varchar to bigint"? Doesn't IsNumeric make the CAST safe? I've tried every numeric datatype in the cast and get the same "Error converting..." error. I don't believe the size of the resulting number is a problem because overflow is a different error.

有趣的是，在 management studio 中，结果实际上会在错误再次出现之前在结果窗格中显示一秒钟.

The interesting thing is, in management studio, the results actually show up in the results pane for a split second before the error comes back.

SELECT CAST(myVarcharColumn AS bigint)  
FROM myTable  
WHERE IsNumeric(myVarcharColumn) = 1 AND myVarcharColumn IS NOT NULL  
GROUP BY myVarcharColumn

有什么想法吗?

推荐答案

IsNumeric 返回 1 如果 varchar 值可以转换为任何数字类型.这包括 int、bigint、decimal、numeric、real &浮动.

IsNumeric returns 1 if the varchar value can be converted to ANY number type. This includes int, bigint, decimal, numeric, real & float.

科学记数法可能会给您带来问题.例如:

Scientific notation could be causing you a problem. For example:

Declare @Temp Table(Data VarChar(20))

Insert Into @Temp Values(NULL)
Insert Into @Temp Values('1')
Insert Into @Temp Values('1e4')
Insert Into @Temp Values('Not a number')

Select Cast(Data as bigint)
From   @Temp
Where  IsNumeric(Data) = 1 And Data Is Not NULL

有一个技巧可以与 IsNumeric 一起使用，以便它返回 0 以使用科学记数法表示的数字.您可以应用类似的技巧来防止十进制值.

There is a trick you can use with IsNumeric so that it returns 0 for numbers with scientific notation. You can apply a similar trick to prevent decimal values.

IsNumeric(YourColumn + 'e0')

IsNumeric(YourColumn + 'e0')

IsNumeric(YourColumn + '.0e0')

IsNumeric(YourColumn + '.0e0')

试试看.

SELECT CAST(myVarcharColumn AS bigint)
FROM myTable
WHERE IsNumeric(myVarcharColumn + '.0e0') = 1 AND myVarcharColumn IS NOT NULL
GROUP BY myVarcharColumn

这篇关于CAST 和 IsNumeric的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！