Given a set of space delimited search terms like "winchester hotel", how can I search a column to see if it includes all of the words without using either full-text or dynamic SQL?
Annoyingly, I cannot use fulltext indexing in this instance as I can only search the View, which cannot be indexed itself due to having no possible unique index of it's own.
I've definitely achieved this before and I seem to recall it involved inserting the search words in to a table variable.
You should have/make a function which splits the line into a "table" valued result - for example a temporary table. A ton of these exists, so they should be easy to find.
Then you can join that result table into your base table containing the date using like, in or similar.
Then you can group by the result from your base table and count the number of occurances, and if the count of the result is the same as the number of fields - then you know it contains all words.
It's not exactly a high performing method though, so chances are, it might be better to do some of it in an application code layer, if the performance hit is too high.
