SQL IsNumeric 返回 True，但 SQL 报告“转换失败”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/570075/
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 Returns True but SQL Reports 'Conversion Failed'
提问by Kyle B.
Assuming the following data:
假设以下数据：
Column1 (data type: varchar(50))
--------
11.6
-1
1,000
10"
Non-Numeric String
I have a query, which is pulling data from this column and would like to determine if the value is a number, then return it as such in my query. So I am doing the following
我有一个查询，它从该列中提取数据，并想确定该值是否为数字，然后在我的查询中将其返回。所以我正在做以下事情
SELECT CASE
WHEN IsNumeric(Replace(Column1, '"', '')) = 1 THEN Replace(Column1, '"', '')
ELSE 0
END AS NumericValue
SQL is reporting back:
SQL 正在报告：
Conversion failed when converting the varchar value '11.6' to data type int.
将 varchar 值“11.6”转换为数据类型 int 时转换失败。
Why? I have also tried to force cast this:
为什么？我也尝试强制转换：
SELECT CASE
WHEN IsNumeric(Replace(Column1, '"', '')) = 1 THEN cast(Replace(Column1, '"', '') AS float)
ELSE 0
END AS NumericValue
And I got:
我得到了：
Error converting data type varchar to float.
将数据类型 varchar 转换为 float 时出错。
采纳答案by Quassnoi
You need to replace comma with a period:
您需要用句点替换逗号：
CAST(REPLACE(column, ',', '.') AS FLOAT)
SQL Serveroutputs decimal separator defined with locale, but does not unterstand anything but a period in CASTs to numeric types.
SQL Server输出用语言环境定义的小数点分隔符，但除了将CASTs 中的句点转换为数字类型外，什么都不理解。
回答by Polina F.
First convert the string to money, then covert it to any other numeric format since money type gives a true numeric string always. You will never see an error then.
首先将字符串转换为货币，然后将其转换为任何其他数字格式，因为货币类型始终提供真正的数字字符串。那时您将永远不会看到错误。
Try the following in your query, and you'll know what I am talking about. Both will return 2345.5656. The Money datatype is rounded to 4 decimal places, and hence the casting causes rounding to 4 decimal places.
在您的查询中尝试以下操作，您就会知道我在说什么。两者都将返回 2345.5656。Money 数据类型四舍五入到小数点后 4 位，因此转换会导致四舍五入到小数点后 4 位。
SELECT CAST('2,345.56556' as money), CAST(',345.56556' as money)
Cast( cast('2,344' as money) as float) will work perfectly or cast( cast('2,344' as money) as decimal(7,2)) will also work.
Cast( cast('2,344' as money) as float) 可以完美工作，或者 cast( cast('2,344' as money) as decimal(7,2)) 也可以。
Even cast(CAST('$2,345.56556' as money) as int ) will work perfectly rounding it to nearest integer.
即使 cast(CAST('$2,345.56556' as money) as int ) 也可以完美地将其四舍五入到最接近的整数。
回答by brendan
There are many issues with SQL isnumeric. For example:
SQL isnumeric 有很多问题。例如：
select isnumeric('1e5')
This will return 1 but in many languages if you try to convert it to a number it will fail. A better approach is to create your own user defined function with the parameters you need to check for:
这将返回 1 但在许多语言中，如果您尝试将其转换为数字，它将失败。更好的方法是使用您需要检查的参数创建您自己的用户定义函数：
回答by user489390
IsNumeric(' ')also returns 1, but then CAST as int blows up. Brendan above says write your own function. He is correct.
IsNumeric(' ')也返回 1，但是 CAST as int 爆炸了。Brendan 上面说写你自己的函数。他是对的。
回答by dotjoe
ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type;
当输入表达式计算为有效整数、浮点数、货币或十进制类型时，ISNUMERIC 返回 1；
So the problem is it is a valid number but not a valid int.
所以问题是它是一个有效的数字，但不是一个有效的整数。
回答by Lieven Keersmaekers
Kyle,
凯尔，
I think this solves the problem. The problem lies in the fact that the ELSE clause initializes your result to be an INTEGER. By making an explicit typecast to FLOAT and adding the suggestion of Quassnoi, it seems to work.
我认为这可以解决问题。问题在于 ELSE 子句将您的结果初始化为整数。通过对 FLOAT 进行显式类型转换并添加 Quassnoi 的建议，它似乎有效。
DECLARE @MyTable TABLE (Column1 VARCHAR(50))
INSERT INTO @MyTable VALUES('11.6')
INSERT INTO @MyTable VALUES('-1')
INSERT INTO @MyTable VALUES('1,000')
INSERT INTO @MyTable VALUES('10" ')
INSERT INTO @MyTable VALUES('Non-Numeric String')
SELECT CASE WHEN ISNUMERIC(REPLACE(Column1,'"','')) = 1 THEN REPLACE(REPLACE(Column1,'"',''), ',', '.') ELSE CAST(0 AS FLOAT) END
FROM @MyTable
Regards,
Lieven
问候， 利
文
回答by user2284133
This solution does not work in all cases (specifically numbers with money and/or thousand separators). Concatenate an exponent representation to the end of the number which is represented by a string...ISNUMERIC() works fine from there. Examples below:
此解决方案不适用于所有情况（特别是带有货币和/或千位分隔符的数字）。将指数表示连接到由字符串表示的数字的末尾...ISNUMERIC() 从那里可以正常工作。下面的例子：
-- CURRENT ISNUMERIC RESULTS
SELECT ISNUMERIC('11.6'); --1
SELECT ISNUMERIC ('-1'); --1
SELECT ISNUMERIC('1,000'); --1
SELECT ISNUMERIC('10"'); --0
SELECT ISNUMERIC(''); --1
-- NEW ISNUMERIC RESULTS
SELECT ISNUMERIC('11.6'+'e+00'); --1
SELECT ISNUMERIC ('-1'+'e+00'); --1
SELECT ISNUMERIC('1,000'+'e+00'); --0
SELECT ISNUMERIC('10"'+'e+00'); --0
SELECT ISNUMERIC(''+'e+00'); --0
This, at the very least, standardizes the format for using the REPLACE() function.
这至少标准化了使用 REPLACE() 函数的格式。
回答by Hung Doan
I have just meet this issue.
我刚遇到这个问题。
You can try this solution if you don't mind about limitation of decimal length.
如果您不介意十进制长度的限制，您可以尝试此解决方案。
CONVERT(numeric, CONVERT(money, '.'))
NOTE:
笔记：
- It is supportedin SQL Server 2008or above.
- Money rangeis : -922,337,203,685,477.5808 to 922,337,203,685,477.5807 - four decimals.
- 它支持在SQL Server 2008中或以上。
- 货币范围是：-922,337,203,685,477.5808 到 922,337,203,685,477.5807 -四位小数。
