Table A has column X, which is an int made up of the concatenation of columns Y and Z (which are both floats) in table B. I want to join tables A and B in a manner similar to this:
select *
from tableA a inner join tableB b
on a.X = b.cast(concat(cast(b.Y as varchar), cast(b.Z as varchar)) as integer
Except that, obviously, my example is not correctly done.
Can you create another column in b named x which contains the value you want? Then the join to A is easy.
You can do this:
select *
from tableA a
inner join tableB b
on a.X = cast(cast(b.Y as varchar) + cast(b.Z as varchar) as int)
If either of your floats have decimal points though, the conversion to int will fail.
E.g., this works:
declare @f1 as float
declare @f2 as float
set @f1 = 1
set @f2 = 7
select cast(cast(@f1 as varchar) + cast(@f2 as varchar) as int)
Output: 17
But this does not:
declare @f1 as float
declare @f2 as float
set @f1 = 1.3
set @f2 = 7
select cast(cast(@f1 as varchar) + cast(@f2 as varchar) as int)
Output: Conversion failed when converting the varchar value '1.37' to data type int.
Sounds like a job for a computed column, then it would be index-able.