将 varchar 转换为 float 时出错 [英] error converting varchar to float
问题描述
将 varchar 转换为浮点数时出错.
Getting an error converting varchar to float.
有一个带有 result 列的表格(不是我制作的)，有 varchar 数据.我想转换为浮点数以获取所有大于 180.0 的值.
Have a table (not of my making) with column result, having varchar data. I'd like to convert to float to grab all values > 180.0.
SELECT result FROM table WHERE result > 180.0
产生错误.有趣的是:
WITH temp AS (
CASE
WHEN ISNUMERIC(result)=1 THEN CAST(result as FLOAT)
ELSE CAST(-1.0 AS FLOAT)
END AS result
)
这运行良好.当我配对时:
This runs fine. When I pair it with:
SELECT temp.result FROM temp WHERE temp.result > 180.0
我再次收到错误消息.想法?
I get the error again. Thoughts?
更新:请求完整代码...
UPDATE: Full code Requested...
WITH temp AS (
SELECT
CASE
WHEN ISNUMERIC(result)=1 THEN CAST(result as FLOAT)
ELSE CAST(-1.0 AS FLOAT)
END AS result
FROM table)
SELECT temp.result FROM temp WHERE temp.result > 180.0
使用 SQL-SERVER 2008 RC2.
Using SQL-SERVER 2008 RC2.
推荐答案
这意味着表中至少有一行不能转换为 float.执行CASE 是安全的，但是在编写T-SQL 时，结合CTE 并添加WHERE 子句会陷入程序员的常见谬误:声明顺序意味着执行顺序.程序员习惯于 C 类语言的命令式过程风格，无法理解 SQL 基于声明集的性质.我之前写过关于这个问题的文章，并举例说明了谬误导致错误的情况:
It means you have at least one row in the table that cannot be cast to float. Doing the CASE is safe, but combining the CTE and adding a WHERE clause falls into a common fallacy of programmers when writing T-SQL: that order of declaration implies an order of execution. Programmers are used to the imperative procedural style of C like languages and fail to comprehend the declarative set based nature of SQL. I have wrote before about this issue and gave examples when the fallacy causes errors:
一旦您发布了完整的代码，我们就可以看到您在案例中究竟在哪里犯了谬论并假设了特定的执行顺序.
Once you post your full code we can see where exactly did you make the fallacy in your case and assumed a certain order of execution.
更新后
好的，所以我必须管理，在您的情况下，代码在执行顺序上是正确的，result 列不能在没有首先评估 CASE.如果 CASE 位于 WHERE 子句中，情况就会有所不同.
OK, so I have to admin that in your case the code is correct in the order of execution, the result column cannot be projected w/o first evaluating the CASE. Had the CASE been in a WHERE clause things would have been different.
您的问题有所不同:ISNUMERIC.这个函数对NUMERIC的含义非常了解，之前已经坑过很多开发者.即，它接受 CAST 和 CONVERT 将拒绝的值.像包含逗号的那些:
Your problem is different: ISNUMERIC. This function has a very generous understanding of what NUMERIC means and has bitten many developers before. Namely, it accepts values that CAST and CONVERT will reject. Like ones containing a comma:
declare @n varchar(8000) = '1,000';
select isnumeric(@n);
select cast(@n as float);
select case when isnumeric(@n)=1 then cast(@n as float) else null end;
因此，您的值通过了 ISNUMERIC 测试但无法转换.提醒一下，您越深入研究这种方法，就会发现越多的关门.只是没有安全的方法可以在服务器端进行您需要的演员表.理想情况下，修复数据模型(如果字段存储浮点数，则使该字段成为浮点数).除此之外，对数据进行分类并删除所有不是适当浮点数的值，并修复前端/应用程序以不再引入新的值，然后添加一个约束，如果出现新的错误值将触发错误.您将无法在查询中解决此问题，那条道路上到处都是尸体.
So you have values that pass the ISNUMERIC test but fail to convert. Just a heads up, the more you'll digg into this approach, the more closed doors you'll find. Is just no safe way to do the cast you need on the server side. Ideally, fix the data model (make the field a float if it stores floats). Short of that, triage the data and remove all values that are not a proper float, and fix the front-end/application to no longer introduce new ones, then add a constraint that will trigger the error if new bad values appear. You won't be able to solve this in a query, that road is littered with bodies.
在下一版本的 SQL Server 中，您将拥有一个新功能，TRY_CONVERT，这将解决您的问题.
With the next version of SQL Server you will have a new function, TRY_CONVERT, that would solve your problem.
这篇关于将 varchar 转换为 float 时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
