SQL: Find rows where Column contains all of the given words

Question

I have some column EntityName, and I want to have users to be able to search names by entering words separated by space. The space is implicitly considered as an 'AND' operator, meaning that the returned rows must have all of the words specified, and not necessarily in the given order.

For example, if we have rows like these:

1. abba nina pretty balerina
2. acdc you shook me all night long
3. sth you are me
4. dream theater it's all about you

when the user enters: me you, or you me (the results must be equivalent), the result has rows 2 and 3.

I know I can go like:

WHERE Col1 LIKE '%' + word1 + '%'
  AND Col1 LIKE '%' + word2 + '%'

but I wanted to know if there's some more optimal solution.

The CONTAINS would require a full text index, which (for various reasons) is not an option.

Maybe Sql2008 has some built-in, semi-hidden solution for these cases?

Answer 1

The only thing I can think of is to write a CLR function that does the LIKE comparisons. This should be many times faster.

Update: Now that I think about it, it makes sense CLR would not help. Two other ideas:

1 - Try indexing Col1 and do this:

WHERE (Col1 LIKE word1 + '%' or Col1 LIKE '%' + word1 + '%')
  AND (Col1 LIKE word2 + '%' or Col1 LIKE '%' + word2 + '%')

Depending on the most common searches (starts with vs. substring), this may offer an improvement.

2 - Add your own full text indexing table where each word is a row in the table. Then you can index properly.