SQL 将数据类型 varchar 转换为 float 时出错
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14952004/
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
Error converting data type varchar to float
提问by user2086052
Searched and searched on SO and can't figure it out
Tried CASTING each field as FLOAT to no avail, convert didn't get me any further
How can I get the below case clause to return the value stated in the THEN section?
在 SO 上搜索和搜索，但无法弄清楚
尝试将每个字段转换为 FLOAT 无济于事，转换没有让我进一步了解
如何获得以下 case 子句以返回 THEN 部分中所述的值？
Error: Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to float.
错误：消息 8114，级别 16，状态 5，第 1 行将数据类型 varchar 转换为浮点数时出错。
section of my SQL query that makes it error:
我的 SQL 查询的部分使其出错：
When cust_trendd_w_costsv.terms_code like '%[%]%' and (prod.dbo.BTYS2012.average_days_pay) - (substring(cust_trendd_w_costsv.terms_code,3,2)) <= 5 THEN prod.dbo.cust_trendd_w_costsv.terms_code
average_days_pay = float
terms_code = char
average_days_pay = 浮动
条款_代码 = 字符
Cheers!
干杯!
回答by valex
Try to use ISNUMERICto handle strings which can't be converted:
尝试使用ISNUMERIC处理无法转换的字符串：
When cust_trendd_w_costsv.terms_code like '%[%]%'
and (prod.dbo.BTYS2012.average_days_pay) -
(case when isnumeric(substring(cust_trendd_w_costsv.terms_code,3,2))=1
then cast(substring(cust_trendd_w_costsv.terms_code,3,2) as float)
else 0
end)
<= 5 THEN prod.dbo.cust_trendd_w_costsv.terms_code
回答by Damien_The_Unbeliever
The issue that you're having is that you're specifically searching for strings that contain a %character, and then converting them (implicitly or explicitly) to float.
您遇到的问题是您专门搜索包含%字符的字符串，然后将它们（隐式或显式）转换为浮点数。
But strings containing %signs can't be converted to float whilst they still have a %in them. This also produces an error:
但是包含%符号的字符串不能在它们仍然包含 a 时转换为浮点数%。这也会产生错误：
select CONVERT(float,'12.5%')
If you're wanting to convert to float, you'll need to remove the %sign first, something like:
如果要转换为浮点数，则需要先删除%符号，例如：
CONVERT(float,REPLACE(terms_code,'%',''))
will just eliminate it. I'm not sure if there are any other characters in your terms_codecolumn that may also trip it up.
只会消除它。我不确定您的terms_code专栏中是否还有其他字符也可能会绊倒它。
You also need to be aware that SQL Server can quite aggressively re-order operations and so may attempt the above conversion on other strings in terms_code, even those not containing %. If that's the source of your error, then you need to prevent this aggressive re-ordering. Provided there are no aggregates involved, a CASEexpression can usually avoid the worst of the issues - make sure that all strings that you don't want to deal with are eliminated by earlier WHENclauses before you attempt your conversion
您还需要注意 SQL Server 可以非常积极地重新排序操作，因此可能会尝试对 中的其他字符串进行上述转换terms_code，即使是那些不包含%. 如果这是您错误的根源，那么您需要防止这种激进的重新排序。如果不涉及聚合，CASE表达式通常可以避免最糟糕的问题 -WHEN在尝试转换之前，请确保您不想处理的所有字符串都被前面的子句消除
回答by Maryam Arshi
If your are sure that Substring Part returns a numeric value, You can Cast The substring(....) to Float :
如果您确定 Substring Part 返回一个数值，您可以将 substring(....) 转换为 Float ：
.....and (prod.dbo.BTYS2012.average_days_pay) - (CAST(substring(cust_trendd_w_costsv.terms_code,3,2)) as float ) <= 5 ....
