Can I Compare Varchar To Float In Sql ?

Question

1.00/5 (1 vote)

See more:

HI,

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 !

Posted 28-Jan-16 4:10am

Member 12271876

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ZurdoDev · Answer 1 · 2016-01-28T04:20:00

Solution 1

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.

SQL

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

Posted 28-Jan-16 4:20am

ZurdoDev

Comments

Sergey Alexandrovich Kryukov 28-Jan-16 10:51am

Even comparing float to float for equality is a bad idea. The valid criterion is that the difference between the values is "small enough" :-)
Generally, only the equality to certain accuracy makes real sense.
—SA

Member 12271876 28-Jan-16 10:55am

There is no error.. But nothing happens.

ZurdoDev 28-Jan-16 11:03am

Then the sql is not finding anything.

This sql will update table 1's Revenue with the value from table 2 where the bus_id is the same in both tables or where the bus_name is the same in both tables.

Member 12271876 28-Jan-16 11:05am

Yes, sql is unable to find matching records.
If i do search on specific Bus_id records, I am able to see same records in both tables.
Not sure Y sql is unable to find it. is there any other way to tackle the problem..?
I am matching by Bus_id only now.
Update t1
set t1.Revenue = t2.revenue
from t1
Inner Join t2
on t1.Bus_id = CONVERT(NVARCHAR(50), t2.Bus_id);

ZurdoDev 28-Jan-16 11:07am

I would suggest doing something like this and then analyzing the results. Do you get anything results?

SELECT t1.bus_id, t1.Revenue, t2.bus_id, t2.revenue
FROM t1
Inner Join t2
on t1.Bus_id = CONVERT(NVARCHAR(50), t2.Bus_id);

Member 12271876 28-Jan-16 11:11am

I get no value when i do this.Just blank table with headers.

ZurdoDev 28-Jan-16 11:14am

So, then join is not working.
You may need to trim your data. I would look at two records in each table and see why the join won't work. If you imported from excel, I have seen it where there are special characters at the end, ones you can't see when running queries.

Member 12271876 28-Jan-16 11:18am

Ok.. will check. Thanks !

Member 12271876 28-Jan-16 12:07pm

I deleted the records(there were not many just 4 records ) that had issues like 203d and trying loading the file again by using bulk insert.
query - BULK INSERT t1
FROM 'C:\Users\abc\Desktop\Newfile.txt'
WITH (FIELDTERMINATOR = '|');

its giving me message - Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (Bus_id).

I delared Bus_id as float this time.

Can u help..?

ZurdoDev 28-Jan-16 13:20pm

I'm not sure. I'd google the error.