Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:


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


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


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


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


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.

share|improve this question
up vote 4 down vote accepted
  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

share|improve this answer
Sweet! Thanks! One thing I read is that full text search (which was what I was considering using initially) is intended more for large blocks of text rather than narrow columns. Regardless, your solution works great and will be fine for now. Will do more research as to whether or not I should use full text. I was trying to figure out how to do this with a subquery. Your solution is nice and simple (now that I've seen it!). – birdus Jul 8 '11 at 21:05

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.