if the logic is like this then you may use RIGHT (not in where becouse there is performance problem)

you can try on this way:

select * from TABLE1 t join

( select *, right(code,1)code form TABLE2 )q on t.id = q.code and t.name = q.name

or

select *, right(code,1)code into #temp form TABLE2

and after that you can join two tables.