Converting user-entered search query to where clau

问题:

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.

回答1:

This may not be exactly what you are looking for but it may offer you some further ideas.

http://www.sqlservercentral.com/articles/Full-Text+Search+(2008)/64248/

回答2:

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);
   

回答3:

In addition to @franzo's answer above you probably also want to change the default stop word behaviour in SQL. Otherwise queries containing single digit numbers (or other stop words) will not return any results.

Either disable stop words, create your own stop word list and/or set noise words to be transformed as explained in SQL 2008: Turn off Stop Words for Full Text Search Query

To view the system list of (English) sql stop words, run:

select * from sys.fulltext_system_stopwords where language_id = 1033

回答4:

I realize it's a bit of a side-step from your original question, but have you considered moving away from SQL fulltext indexes and using something like Lucene/Solr instead?

回答5:

The easiest way to do this is to use dynamic SQL (I know, insert security issues here) and break the phrase into a correctly formatted string.

You can use a function to break the phrase into a table variable that you can use to create the new string.

回答6:

A combination of GoldParser and Calitha should sort you out here.

This article: http://www.15seconds.com/issue/070719.htm has a googleToSql class as well, which does some of the translation for you.