I have a table with a "Description" field. Which contains the titles of a whole lot of phone models.

Id - Int (PK)
Description - nvarchar(200)

**Lookup Table**
LookupId - Int (PK)
SearchString - nvarchar(50)
Result - nvarchar(100)

I then have another lookup table with all the different models of phones I want to search for.

If I was going to do a text search on the description field I could easily write this by doing

SELECT * FROM Listing WHERE Description Like '%SearchTerm%'

However I want to somehow do a search on each record in the Lookup Table. Im thinking that I probably have to do a loop for each record in the Lookup table and then write some dynamic sql? Is there an easier way than this?


If I understand your question correctly, you basically want to join your [Table1] to [Lookup Table] whenever [Table1].[Description] is like [Lookup Table].[SearchString].

You can try this:

select * from Table1 
join [Lookup Table] 
on Table1.Description like ('%' + [Lookup Table].SearchString + '%');

This will only return records where there is a match on the like condition. You can adapt this to an outer join if you need all rows from one of the tables, as required.

I won't vouch for the performance of this query. Also, it is an adaptation of another question on Stack Exchange.

| improve this answer | |
  • Thank you for this! Its a one of process so not concerned with the performance :) – Glen Hong Oct 15 '18 at 6:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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