This is what i came up with. Try it to see if applies.
create table A (id int, code varchar(5))
create table B (id int, code varchar(5))
insert into A values (1,'XYZ')
insert into B values (2,'XYZ')
insert into B values (3,'AXYZ')
select distinct case when charindex(A.code, B.code) is not null then B.id end from B,A
PS: I tested the code in mysql and adapt to the charindex function.
Also this is a quick fix, because it presents duplicate records that we "eliminate" with the disticnt, beacuse the tables are not joined making a cartesian product.
To get the non mactching results just change to null.