Partial String match SQL - Inner Join

Question

Trying to do a partial string match here but am getting a problem with the LIKE operator. I'm sure its the syntax but I cannot see it

SELECT Name
FROM Table1 a
INNER JOIN Table2 b ON a.Name = b.FullName LIKE '%' + a.Name + '%'

I get an error message when I execute this

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'LIKE'.

Answer 1

Try this

SELECT distinct Name, FullName
FROM Table1 a
INNER JOIN Table2 b ON (b.FullName LIKE '%' + a.Name + '%' OR a.Name like '%'+b.FullName+'%')

Answer 2

I had this exact issue with Postgres. Its a bit messy, but I used textcat to add the wildcard since the above '%'+ didn't play well with my query on Metabase

Try this:

SELECT distinct Name, FullName
FROM Table1 a
INNER JOIN Table2 b ON b.FullName LIKE textcat(textcat('%',a.Name),'%')