SQL IsNumeric 不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7567804/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL IsNumeric not working
提问by Hoody
The reserve column is a varchar, to perform sums on it I want to cast it to a deciaml. But the SQL below gives me an error
保留列是一个 varchar，要对其进行求和，我想将其转换为 deciaml。但是下面的 SQL 给了我一个错误
select
cast(Reserve as decimal)
from MyReserves
Error converting data type varchar to numeric.
将数据类型 varchar 转换为数字时出错。
I added the isnumeric and not null to try and avoid this error but it still persists, any ideas why?
我添加了 isnumeric 而不是 null 来尝试避免这个错误，但它仍然存在，任何想法为什么？
select
cast(Reserve as decimal)
from MyReserves
where isnumeric(Reserve ) = 1
and MyReserves is not null
采纳答案by Salazaar
It seems that isnumeric has some Problems:
似乎 isnumeric 有一些问题：
http://www.sqlhacks.com/Retrieve/Isnumeric-problems(via internet archive)
http://www.sqlhacks.com/Retrieve/Isnumeric-problems（通过互联网存档）
According to that Link you can solve it like that:
根据该链接，您可以这样解决：
select
cast(Reserve as decimal)
from MyReserves
where MyReserves is not null
and MyReserves * 1 = MyReserves
回答by Dalex
See here: CAST and IsNumeric
请参见此处：CAST 和 IsNumeric
Try this:
尝试这个：
WHERE IsNumeric(Reserve + '.0e0') = 1 AND reserve IS NOT NULL
UPDATE
更新
Default of decimal is (18,0), so
十进制的默认值是 (18,0)，所以
declare @i nvarchar(100)='12121212121211212122121'--length is>18
SELECT ISNUMERIC(@i) --gives 1
SELECT CAST(@i as decimal)--throws an error
回答by Gordon Linoff
Gosh, nobody seems to have explained this correctly. SQL is a descriptive language. It does not specify the order of operations.
天哪，似乎没有人正确解释过这一点。SQL 是一种描述性语言。它没有指定操作的顺序。
The problem that you are (well, were) having is that the wheredoes not do the filtering before the conversion takes place. Order of operations, though, is guaranteed for a case statement. So, the following will work:
您（好吧，曾经）遇到的问题是where在转换发生之前没有进行过滤。但是，操作顺序对于 case 语句是有保证的。因此，以下将起作用：
select cast(case when isnumeric(Reserve) = 1 then Reserve end as decimal)
from MyReserves
where isnumeric(Reserve ) = 1 and MyReserves is not null
The issue has nothing to do with the particular numeric format you are converting to or with the isnumeric()function. It is simply that the ordering of operations is not guaranteed.
该问题与您要转换为的特定数字格式或isnumeric()函数无关。只是无法保证操作的顺序。
回答by renegm
Use try_cast (sql 2012)
使用 try_cast (sql 2012)
select
try_cast(Reserve as decimal)
from MyReserves
回答by Derek Tomes
IsNumeric is a problem child -- SQL 2012 and later has TRY_CAST and TRY_CONVERT
IsNumeric 是一个问题孩子——SQL 2012 及更高版本有TRY_CAST 和 TRY_CONVERT
If you're on an earlier version then you can write a function that'll convert to a decimal (or NULL if it won't convert). This uses the XML conversion functions that don't throw errors when the number won't fit ;)
如果您使用的是早期版本，那么您可以编写一个函数来转换为十进制（如果不转换则为 NULL）。这使用了 XML 转换函数，当数字不合适时不会抛出错误；)
-- Create function to convert a varchar to a decimal (returns null if it fails)
IF EXISTS( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID( N'[dbo].[ToDecimal]' ) AND type IN( N'FN',N'IF',N'TF',N'FS',N'FT' ))
DROP FUNCTION [dbo].[ToDecimal];
GO
CREATE FUNCTION ToDecimal
(
@Value VARCHAR(MAX)
)
RETURNS DECIMAL(18,8)
AS
BEGIN
-- Uses XML/XPath to convert @Value to Decimal because it returns NULL it doesn't cast correctly
DECLARE @ValueAsXml XML
SELECT @ValueAsXml = Col FROM (SELECT (SELECT @Value as Value FOR XMl RAW, ELEMENTS) AS Col) AS test
DECLARE @Result DECIMAL(38,10)
-- XML/XPath will return NULL if the VARCHAR can't be converted to a DECIMAL(38,10)
SET @Result = @ValueAsXml.value('(/row/Value)[1] cast as xs:decimal?', 'DECIMAL(38,10)')
RETURN CASE -- Check if the number is within the range for a DECIMAL(18,8)
WHEN @Result >= -999999999999999999.99999999 AND @Result <= 999999999999999999.99999999
THEN CONVERT(DECIMAL(18,8),@Result)
ELSE
NULL
END
END
Then just change your query to:
然后只需将您的查询更改为：
select dbo.ToDecimal(Reserve) from MyReserves
回答by Sean Gahan
Just a heads up on isnumeric; if the string contains some numbers and an 'E' followed by some numbers, this is viewed as an exponent. Example, select isnumeric('123E0') returns 1.
只是提醒一下 isnumeric；如果字符串包含一些数字和后跟一些数字的“E”，则将其视为指数。例如，select isnumeric('123E0') 返回 1。
回答by Matthew Chapman
I had this same problem and it turned out to be scientific notation such as '1.72918E-13' To find this just do where Reserve LIKE '%E%'. Try bypassing these and see if it works. You'll have to write code to convert these to something usable or reformat your source file so it doesn't store any numbers using scientific notation.
我遇到了同样的问题，结果是科学记数法，例如“1.72918E-13”要找到这个，只需在 Reserve LIKE '%E%' 处执行。尝试绕过这些，看看它是否有效。您必须编写代码将这些转换为可用的内容或重新格式化您的源文件，使其不使用科学记数法存储任何数字。
回答by ipr101
isnumericis not 100% reliable in SQL - see this question Why does ISNUMERIC('.') return 1?
isnumeric在 SQL 中不是 100% 可靠 - 请参阅此问题为什么 ISNUMERIC('.') 返回 1？
I would guess that you have value in the reserve column that passes the isnumerictest but will not cast to decimal.
我猜你在通过isnumeric测试但不会转换为十进制的储备列中有值。
回答by Barry Kaye
IsNumericis possibly not ideal in your scenario as from the highlighted Note on this MSDN pageit says "ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($)."
IsNumeric在您的场景中可能并不理想，因为从这个MSDN 页面上突出显示的注释中可以看出“对于某些不是数字的字符，例如加号 (+)、减号 (-) 和有效的货币符号，例如美元，ISNUMERIC 返回 1符号（$）。”
Also there is a nice article herewhich further discusses ISNUMERIC.
还有一篇很好的文章here进一步讨论了ISNUMERIC。
回答by Larry Lustig
Try (for example):
尝试（例如）：
select
cast(Reserve as decimal(10,2))
from MyReserves
Numeric/Decimal generally want a precision an scale.
数字/十进制通常需要精度和刻度。
