Join using LIKE ?

I have been given two tables in Excel which I've imported into SQL Server
and I need to join them.
Each of them has a unique Product but there is a bit of inconsistency in
that in Table A, some (but not all) of the Products have been prefixed with
the Supplier Name - I don't have a master list of supplier names yet,
building that will be Stage 2. The Products in Table B are exactly the same
as Table A without the Supplier Name
In other words, the contents of the tables are something like:
Table_A Table_B
Widgets Ltd Special Gizmo Special Gizmo
Widgets Ltd Standard Gizmo Standard Gizmo
Inhouse Special Inhouse Special
Joe Bloggs Standard Gadget Standard Gadget
Joe Bloggs Special Gadget Special Gadget
One Off Product One Off Product
I was thinking of sometthing along lines of
Select *
From Table_A Join Table_B on Table_A.Product LIKE ('%'+TableB.Product)
I can't seem to get the syntax quite right and Googling on using joins with
LIKE can be dodgy.
Any tips or suggestions?here you go
create Table TableA( Product varchar(49))
insert into TableA values ('Widgets Ltd Special Gizmo')
insert into TableA values ('Widgets Ltd Standard Gizmo')
insert into TableA values ('Inhouse Special')
insert into TableA values ('Joe Bloggs Standard Gadget')
insert into TableA values ('Joe Bloggs Special Gadget')
insert into TableA values ('One Off Product')
create Table TableB( Product varchar(49))
insert into TableB values ('Special Gizmo')
insert into TableB values ('Standard Gizmo')
insert into TableB values ('Inhouse Special')
insert into TableB values ('Standard Gadget')
insert into TableB values ('Special Gadget')
insert into TableB values ('One Off Product')
select * from tableB b join TableA a on a.Product like '%' +b.product
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||"SQL" <denis.gobo@.gmail.com> wrote in message
news:1145638327.074438.26100@.t31g2000cwb.googlegroups.com...
> here you go
Thks|||Thx