SQL 错误无法将数据类型 nvarchar 转换为浮点数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26765604/
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 unable to convert data type nvarchar to float
提问by Chidi Okeh
I have searched both this great forum and googled around but unable to resolve this.
我已经搜索了这个很棒的论坛并用谷歌搜索，但无法解决这个问题。
We have two tables (and trust me I have nothing to do with these tables). Both tables have a column called eventId.
我们有两张桌子（相信我，我与这些桌子无关）。两个表都有一个名为eventId.
However, in one table, data type for eventIdis floatand in the other table, it is nvarchar.
然而，在一个表中，数据类型eventId就是float和在另一个表中，这是nvarchar。
We are selecting from table1where eventIis defined as floatand saving that Id into table2where eventIdis defined as nvarchar(50).
我们正在选择table1where eventIis defined asfloat并将该 Id 保存到table2where eventIdis defined as 中nvarchar(50)。
As a result of descrepancy in data types, we are getting error converting datatype nvarcharto float.
由于数据类型不一致，我们在将数据类型转换nvarchar为float.
Without fooling around with the database, I would like to cast the eventIdto get rid of this error.
在不玩弄数据库的情况下，我想强制转换eventId以摆脱此错误。
Any ideas what I am doing wrong with the code below?
任何想法我在下面的代码中做错了什么？
SELECT
CAST(CAST(a.event_id AS NVARCHAR(50)) AS FLOAT) event_id_vre,
回答by dasblinkenlight
The problem is most likely because some of the rows have event_idthat is empty. There are two ways to go about solving this:
问题很可能是因为某些行event_id是空的。有两种方法可以解决这个问题：
- Convert your
floattonvarchar, rather than the other way around- This conversion will always succeed. The only problem here is if the textual representations differ - say, the table withfloat-as-nvarcharuses fewer decimal digits, or - Add a condition to check for empty IDs before the conversion- This may not work if some of the event IDs are non-empty strings, but they are not float-convertible either (e.g. there's a word in the field instead of a number).
- 将您的 转换
float为nvarchar，而不是相反- 这种转换将始终成功。这里唯一的问题是文本表示是否不同 - 例如，带有float-as-的表nvarchar使用较少的十进制数字，或者 - 在转换之前添加一个条件来检查空 ID- 如果某些事件 ID 是非空字符串，这可能不起作用，但它们也不是可浮点转换的（例如，字段中有一个单词而不是数字）。
The second solution would look like this:
第二种解决方案如下所示：
SELECT
case when a.eventid <> ''
then cast(cast(a.event_id as nvarchar(50)) as float)
ELSE 0.0
END AS event_id_vre,
回答by user12294205
Convert float to nvarchar instead of nvarchar to float. Of course!
将 float 转换为 nvarchar 而不是 nvarchar 转换为 float。当然！
