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'))

导致以下错误：

Msg 8114，Level 16，第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屋！