Asp.net fulltext multiple search terms methodology

Question

Hello,

I've got a search box that users can type terms into. I have a table setup with fulltext searching on a string column. Lets say a user types this: "word, office, microsoft" and clicks "search".

Is this the best way to deal with multiple search terms?

(pseudocode)
foreach (string searchWord in searchTerms){    
    select col1 from myTable where contains(fts_column, ‘searchWord’)
}

Is there a way of including the search terms in the sql and not iterating? I'm trying to reduce the amount of calls to the sql server.

Answer 1

Well you could just build your SQL Query Dynamically...

string [] searchWords = searchTerm.Split(",");

string SQL = "SELECT col1 FROM myTable WHERE 1=2";

foreach (string word in searchWords)
{
    SQL = string.Format("{0} OR contains(fts_column, '{1}')", SQL, word);
}

//EXEC SQL...

Obviously this comes with the usual warnings/disclaimers about SQL Injection etc... but the principal is that you would dynamically build up all your clauses and apply them in one query.

Depending on how your interacting with your DB, it might be feasible for you to pass the entire un-split search term into a SPROC and then split & build dynamic SQL inside the stored procedure.

Answer 2

FREETEXT might work for you. It will separate the string into individual words based on word boundaries (word-breaking). Then you'd only have a single SQL call.

MSDN -- FREETEXT

Answer 3

You could do it similar to what you have there: just parse the search terms based on delimiter, and then make a call on each, joining the results together. Alternatively, you can do multiple CONTAINS:

SELECT Name FROM Products WHERE CONTAINS(Name, @Param1) OR CONTAINS(Name, @Param2) etc.

Maybe try both and see which is faster in your environment.