Filter expression for searching "with all the words"

Using VWD I have created a search feature using the LIKE clause. The filter expression on my SQLDataSource allows the user to search the Description field of a database and yield a result that contains the exact word or phrase entered into a textbox. Assuming that the user enters more than one word, my understanding is that the search result is limited to database rows that contain the EXACT phrase (such as found in an advanced Google search using the “with the exact phrase” option). The current filter expression is:

Description LIKE '%{0}%'

For example, if “John Smith” is typed into the search textbox, the results will include a row with:

1. “John Smith is my neighbor”

but NOT a row with

2. “John is my neighbor. His last name is Smith”.

How does one modify the filter expression so that the search result is like the Google “with all the words” search option, where the search results are limited to records in which all the words typed into the textbox are present but not necessarily in the EXACT continuous order? In the example above, BOTH Descriptions would be returned in the search results when “John Smith” in typed into the search textbox.

Thanks for any help you can provide in helping me refine my search options.

Sounds like you're looking for a "full-text search".

read this:http://msdn2.microsoft.com/en-us/library/ms142571.aspx

|||

To search several words,like "John Smith" ,you have to split that into "john" and "smith " then use

Description LIKE '%John%' andDescription LIKE '%Smith%'

but that operation is quite expensive,I recommend you to use fulltext search. You can see the following like for details:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_fulltextsearch.asp

|||

Thanks for the suggestions. I will explore these options.

I do have a couple of general questions about doing searches:

1. Am I correct in assuming that when you use LIKE as I have done (Description LIKE '%{0}%') that the search results that are returned are for the EXACT word or phrase that was searched (i.e., comparable in accuracy - if not speed - to the Google exact phrase search)?

2. Since full-text searches rely on an index they do not necessarily return EXACT results because "noise" words are eliminated? Thus the best that can be hoped for is an exact word order, but not an exact phrase?

3. Can the combined filter expression cited above (Description LIKE '%John%' and Description LIKE '%Smith%) contain parameters instead of the hardcoded search terms (Description LIKE '%{0}%' andDescription LIKE '%{0}%) that allow any combination of terms to be entered into the search textbox?

I look forward to researching the full-text approach and getting it implemented into my search page. Thanks again.

sql