将数据类型 varchar 转换为 float 时出错 [英] Error converting data type varchar to float

问题描述

在SO上搜索和搜索，无法弄清楚
尝试将每个字段转换为 FLOAT 无济于事，转换没有让我更进一步
如何让下面的 case 子句返回 THEN 部分中所述的值?

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?

错误:Msg 8114, Level 16, State 5, Line 1将数据类型 varchar 转换为 float 时出错.

Error: Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to float.

我的 SQL 查询出错的部分:

section of my SQL query that makes it error:

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 = 浮动
term_code = 字符

average_days_pay = float
terms_code = char

干杯！

推荐答案

尝试使用 ISNUMERIC 处理无法转换的字符串:

Try to use ISNUMERIC to handle strings which can't be converted:

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

这篇关于将数据类型 varchar 转换为 float 时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！