MathFan MathFan - 7 days ago 6
SQL Question

Join does not work on two identical fields

I am trying to join two tables on sql server 2008.

select *
from TABLE1 a
left join TABLE2 b
on a.cont_id = b.cont_id


However, it is not able to connect any of the data.

a.cont_id is of nvarchar(255) type and b.cont_id is varchar(80). Using CAST in any way didn't help.

As a.cont_id consists of only numerical data, at first I used type float and integer and applied cast function, but that didn't solve anything.

I checked their varbinary codes by using the following query

select cont_id, CAST(cont_id AS varbinary) AS BinaryCode
from Table1
where cont_id = '53126124'

select cont_id, CAST(cont_id AS varbinary) AS BinaryCode
from Table2
where cont_id = '53126124'


and I got these two results

cont_id BinaryCode
53126124 0x4189551F60000000


cont_id BinaryCode
53126124 0x3533313236313234


One more information - Table 1 is imported from the excel document that was created in neighbour country (Czech republic).

As for the source tables, here is an example, if it helps.

cont_id C1 C2 C3
53126124 3 31 510
175553321 5 16 510


And

dt cont_id D1 D2
201610 53126124 0 0
201610 175553321 41 520


Do you have any idea how to deal with this?

Answer

Okay, I think I've got it:

This

SELECT CAST(CAST(53126124 AS FLOAT) AS VARBINARY)

results in 0x4189551F60000000, while this

SELECT CAST('53126124' AS varbinary)

results in 0x3533313236313234, while this

SELECT CAST(53126124 AS varbinary)

comes back with 0x032AA3EC.

The first value is the varbinary representation of a FLOAT, the second of a VARCHAR and the last of an INT.

Obviously the first IS NOT VARCHAR!

Float types tend to be unprecise, there might be a tiny value like 1,0000000001 or 0.999999998 which is considered as 1, but is not equal.

You might try with this:

select *
from TABLE1 a
left join TABLE2 b
on CAST(ROUND(a.cont_id,0) AS INT) = CAST(ROUND(b.cont_id,0) AS INT)
Comments