Converting user-entered search query to where clause for use in SQL Server full-text search

Question

What's the best way to convert search terms entered by a user, into a query that can be used in a where clause for full-text searching to query a table and get back relevant results? For example, the following query entered by the user:

+"e-mail" +attachment -"word document" -"e-learning"

Should translate into something like:

SELECT * FROM MyTable WHERE (CONTAINS(*, '"e-mail"')) AND (CONTAINS(*, '"attachment"')) AND (NOT CONTAINS(*, '"word document"')) AND (NOT CONTAINS(*, '"e-learning"'))

I'm using a query parser class at the moment, which parses the query entered by users into tokens using a regular expression, and then constructs the where clause from the tokens.

However, given that this is probably a common requirement by a lot of systems using full-text search, I'm curious as to how other developers have approached this problem, and whether there's a better way of doing things.

Answer 1

How to implement the accepted answer using .Net / C# / Entity Framework...

1. Install Irony using nuget.

2. Add the sample class from: http://irony.codeplex.com/SourceControl/latest#Irony.Samples/FullTextSearchQueryConverter/SearchGrammar.cs

3. Write code like this to convert the user-entered string to a query.

   var grammar = new Irony.Samples.FullTextSearch.SearchGrammar();
   var parser = new Irony.Parsing.Parser(grammar);
   var parseTree = parser.Parse(userEnteredSearchString);
   string query = Irony.Samples.FullTextSearch.SearchGrammar.ConvertQuery(parseTree.Root);
   

4. Perhaps write a stored procedure like this:

   create procedure [dbo].[SearchLivingFish]
   
   @Query nvarchar(2000)
   
   as
   
   select *
   from Fish
   inner join containstable(Fish, *, @Query, 100) as ft
   on ft.[Key] = FishId
   where IsLiving = 1
   order by rank desc
   

5. Run the query.

   var fishes = db.SearchLivingFish(query);