Joining 2 Tables Using A Wildcard

I need to Join 2 tables but there is not a true common field. In Table A the ID field contains the ID from Table B BUT it has additional characters before and after the ID. TableA

Solution 1:

You can use like. Not good for performance but it can work:

select *
from tableA a join
     tableB b
     on a.id like '%' + b.id + '%';

Do note that this will likely do unexpected things. For instance, all ids with a value of 10 will match those with 100. Unless you have some way of knowing which characters in a are actually the id field, you probably have to live with this.

And, this situation happens when people want the primary key of a table to mean something. This is a good argument for anonymous primary keys. No one gets the idea to try to interpret it. If someone wants information about it, they can look it up in the appropriate table.

EDIT:

You can write this using an exists clause if you like:

select*from tableA a
whereexists (select1from tableB b where  a.id like'%'+ b.id +'%');

This will not produce duplicate values from tableA when there is more than one matching value.