Query table A for any rows that are like any search term in table B

Question

I need some help writing a select statement (I still don't think in sets!). Here's the scenario:

I have table A with column "Name varchar(20)". I have table B with column "SearchTerm varchar(20)". I want to query A for any rows that are like any search term in B.

Let me give specific data:

Table A has:

Jay
Jim
Frank
George

If table B has 'a', then the results would be:

Jay
Frank

If table B has 'j', then the results would be:

Jay
Jim

If table B has 'y' and 'g' (two rows), then the results would be:

Jay
George

If table B has 'Jay', then the results would be:

Jay

I actually want to be able to apply this idea to be able to search two columns, FirstName and LastName, but I'm guessing that once I see the idea, I'll be able to generalize the solution to search multiple columns.

Answer 1

...
FROM
  TableA
  JOIN
  TableB ON
          TableA.FirstName LIKE '%' + TableB.SearchTerm + '%' OR 
          TableA.LastName LIKE '%' + TableB.SearchTerm + '%'
....

Now, this isn't going to be the best of queries. Consider full text search instead (I'm not too familiar with it)

This OR could be split into a UNION