我可以比较Varchar在Sql中浮动吗？ [英] Can I Compare Varchar To Float In Sql ?

问题描述

HI，



我有两个表，我试图比较两个表中的相同列（Bus_id）。

唯一的问题是在一个表中列被定义为varchar而在其他表中它被定义为float。

现在当我试图比较这两列时，它给出了我的错误消息错误将数据类型varchar转换为float



示例：表1（我已在此处将Bus_id列定义为varchar，因为其中包含类似203d的值）

Bus_id - Bus_name - 费用

101 - Corporate-Null

203d - Northest - Null

309- -Goasta - Null



表2（此处Bus_id列定义为float。请注意 - 表的来源是excel文件，我使用Import实用程序加载它到sql.I尝试将其更改为varchar，但它给了我错误，所以将此col转换为varchar不起作用）



Bus_id - Bus_name - cost

30 - Gloff-20,6000

103 - eastend --5,400

101 - 公司 - 6,500

309 - Goasta - 4,5900





结果:(如果bus_name或Bus_id匹配，目标是将收入从tb2复制到tb1）

Bus_id - Bus_name - cost

101 - Corporate-6,500

203d - Northest - Null

309 - Goasta - 4,5900



查询 -

更新t1

设置t1.Revenue = t2.revenue

来自t1

内部加入t2

在t1.Bus_id = t2.Bus_id或t1.Bus_name = t2.Bus_name;



将数据类型varchar转换为漂浮。



请帮助！

I have two tables and I am trying to compare the same columns(Bus_id) in both the tables.
The only problem is in one table that column is defined as varchar and in other table it is defined as float.
and now when i m trying to compare these two columns, it is giving me error message "Error converting data type varchar to float"

Example: table 1(I have defined Bus_id column as varchar here, as there are values like 203d in it )
Bus_id--Bus_name--cost
101--Corporate-Null
203d--Northest--Null
309--Goasta--Null

Table 2 (Here Bus_id column as defined as float.Please note -The source of the table was excel file and I used Import utility to load it to sql.I tried changing it to varchar but it gave me error, so converting this col to varchar is not working)

Bus_id--Bus_name--cost
30--Gloff-20,6000
103--eastend--5,400
101--Corporate--6,500
309--Goasta--4,5900


Result :(target is to copy revenue from tb2 to tb1 if bus_name or Bus_id matches)
Bus_id--Bus_name--cost
101--Corporate-6,500
203d--Northest--Null
309--Goasta--4,5900

Query -
Update t1
set t1.Revenue = t2.revenue
from t1
Inner Join t2
on t1.Bus_id = t2.Bus_id or t1.Bus_name = t2.Bus_name ;

Error converting data type varchar to float.

Pls help !

推荐答案

你必须将浮点数转换为varchar能够比较它们，因为你有varchar值无法转换为float（例如：203d）



你的sql很接近，只需在你的连接中转换你应该没问题。

You'll have to convert the float to a varchar to be able to compare them since you have varchar values that cannot be converted to float (for example: 203d)

Your sql is close, just convert in your join and you should be fine.

Update t1
set t1.Revenue = t2.revenue
from t1
Inner Join t2
on t1.Bus_id = CONVERT(NVARCHAR(50), t2.Bus_id) or t1.Bus_name = t2.Bus_name

这篇关于我可以比较Varchar在Sql中浮动吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！