CODEWITHSUNDEEP
sql-server
Benjo
Benjo

Reputation: 95

Convert columns to the same data types to use in a join

I am trying to join two tables but the columns being used are two different data types, table#1 = float and table#2 = varchar(8). 

table#1 examples:
5585
985
445566
null

table#2 examples:
005585
000985
445566

In these examples, I get data for only row 445566

How do I cast/convert??? the columns so that all the rows matching return data

Upvotes: 1

Views: 528

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 81970

Declare @Table1 table (SomeField1 float)
Insert Into @Table1 values (5585),(985),(445566),(null)

Declare @Table2 table (SomeField2 varchar(8))
Insert Into @Table2  values ('005585'),('000985'),('445566')


Select A.SomeField1
      ,B.SomeField2
 From @Table1 A
 Join @Table2 B 
   on cast(A.SomeField1 as int) = cast(B.SomeField2 as int)

Returns

SomeField1  SomeField2
5585        005585
985         000985
445566      445566

Upvotes: 1

Robert Columbia
Robert Columbia

Reputation: 6418

This would depend on your exact join criteria. Generally, it is easier and safer to convert from a numeric type to varchar than vice versa, so you could do something like:

SELECT stuff FROM table1
INNER JOIN table2 ON Convert(varchar(8),table1.field) = table2.field

Upvotes: 0

Logar314159
Logar314159

Reputation: 503

use 

CAST('445566' AS FLOAT) = 445566

Upvotes: 1

Related Questions