SQL Matching a string that contains a string

Question

I have two tables in database.

Both tables have a business name column but not always going to be the same.

For example tbl 1 has a business name of 'Aone Dental Practices Limited TA Jaws Dental' and Tbl 2 has a business name of 'Jaws Dental'. I want to be able to join these together as Jaws Dental is visible in both.

I can't seem to get the Like clause working for this.

tried

 Tbl1_BusinesName Like '%' + Tbl2_BusinesName + '%'  

Answer 1

This query should work :

SELECT * 
FROM Table1 T1
LEFT JOIN Table2 T2 ON T1.BusinesName LIKE '%'+TS.BusinesName+'%'

Answer 2

Using EXISTS you can get the expected result:

SELECT *
FROM dbo.TableName1 AS Tbl1
WHERE EXISTS (SELECT 1
              FROM dbo.TableName2 AS Tbl2
              WHERE Tbl1.BusinesName LIKE '%' + Tbl2.BusinesName + '%');