SQL Server 2008:将数据类型 nvarchar 转换为 float 时出错 [英] SQL Server 2008: Error converting data type nvarchar to float

问题描述

目前正在解决运行此 SQL 查询的问题:

Presently troubleshooting a problem where running this SQL query:

UPDATE tblBenchmarkData 
SET OriginalValue = DataValue, OriginalUnitID = DataUnitID, 
    DataValue = CAST(DataValue AS float) * 1.335 
WHERE 
    FieldDataSetID = '6956beeb-a1e7-47f2-96db-0044746ad6d5' 
    AND ZEGCodeID IN 
             (SELECT ZEGCodeID FROM tblZEGCode 
              WHERE(ZEGCode = 'C004') OR 
                   (LEFT(ZEGParentCode, 4) = 'C004'))

导致以下错误:

消息 8114，级别 16，状态 5，第 1 行
将数据类型 nvarchar 转换为 float 时出错.

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

真正奇怪的是，如果我将 UPDATE 更改为 SELECT 以检查检索到的值是数值:

The really odd thing is, if I change the UPDATE to SELECT to inspect the values that are retrieved are numerical values:

SELECT DataValue 
FROM tblBenchmarkData 
WHERE FieldDataSetID = '6956beeb-a1e7-47f2-96db-0044746ad6d5' 
AND ZEGCodeID IN 
         (SELECT ZEGCodeID 
          FROM tblZEGCode WHERE(ZEGCode = 'C004') OR 
                               (LEFT(ZEGParentCode, 4) = 'C004'))

结果如下:

DataValue
2285260
1205310

想使用 TRY_PARSE 或类似的东西；但是，我们运行的是 SQL Server 2008 而不是 SQL Server 2012.有人有什么建议吗?TIA.

Would like to use TRY_PARSE or something like that; however, we are running on SQL Server 2008 rather than SQL Server 2012. Does anyone have any suggestions? TIA.

推荐答案

查看 tblBenchmarkData 的模式定义会很有帮助，但您可以尝试在查询中使用 ISNUMERIC.类似的东西:

It would be helpful to see the schema definition of tblBenchmarkData, but you could try using ISNUMERIC in your query. Something like:

SET DataValue = CASE WHEN ISNUMERIC(DataValue)=1 THEN CAST(DataValue AS float) * 1.335 
                     ELSE 0 END

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