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

I have some columns EntityName, and I have to be able to find names by entering different words from the user's location. Space is considered as an 'end operator', which means that all the words should be specified in the returned rows, and not necessarily in the given order.

For example, if we have these lines:

Abba Nina Sunder Barina

ACDC you shook me all night

< Li> you are me

The dream theater is all about you

When the user enters: you me, or You should me (the result should be equal), the rows of the result are 2 and 3.

I know that I can go like this:

  Where '%' like call1 '+' word1 + '%' and call '1' like '%' + Word2 + '%'  

But I wanted to know if there is any other optimal solution.

In , will need a full text index, which can be (

for these cases in Sql2008 There are some underlying, semi-hidden solutions?

The only thing I can think is to write The function that likes compares this should be fast several times.

Update: Now when I think about it, understand it Comes out that CLR is not helpful Two other thoughts:

1 - Try indexing Col1 and do this:

  WHERE (call 1 like word 1 + '%' or call 1 '%' + word1 + '%') and (like word 2 + '%' or '%' like Col1 '+' word2 + '%')  

Based on common searches (vs. starts with substring), it can offer improvement.

2 - Add your entire text index table where each word has a row in the table, then you are okay Can index from