Project Description

SQL Server's powerful full-text search (FTS) offers efficient ways to search your data. For constructing queries, Microsoft offers several new constructs to SQL Server's query language for retrieving the data you need.

However, while these new constructs may be perfectly acceptable for developers, they are not at all acceptable for your average users. For starters, depending on the form you use, they can be quite cryptic. For example, users won't know to type expressions such as FORMSOF(INFLECTIONAL, rain) AND FORMSOF(THESAURUS, snow).

In addition, most applications are better off handling invalid search queries as good as they can without simply throwing an error at the user. After all, when was the last time Google gave you an error that your search query was not valid? FTS can fail completely for a wide variety of reasons.

Easy Full-Text Search Queries is a very lightweight library that takes Google-like queries and converts them to FTS queries. The goal is to handle as much of invalid queries as possible and always produce a valid FTS query, except for the case where the result has nothing to search for. (In which case, the application should simply indicate no results were found.)

The table below shows some sample output for various inputs.

Input Output
abc FORMSOF(INFLECTIONAL, abc)
~abc FORMSOF(THESAURUS, abc)
+abc "abc"
"abc" "abc"
-abc NOT FORMSOF(INFLECTIONAL, abc)
abc def (FORMSOF(INFLECTIONAL, abc) AND FORMSOF(INFLECTIONAL, def))
abc or def (FORMSOF(INFLECTIONAL, abc) OR FORMSOF(INFLECTIONAL, def))
<abc def> (FORMSOF(INFLECTIONAL, abc) NEAR FORMSOF(INFLECTIONAL, def))
abc and (def or ghi) (FORMSOF(INFLECTIONAL, abc) AND (FORMSOF(INFLECTIONAL, def) OR FORMSOF(INFLECTIONAL, ghi)))

The resulting output could then be passed to a stored procedure as an argument and used something like what is shown below.

SELECT * FROM Articles
WHERE CONTAINS(Articles.*, @FtsQuery)

More information is available in the article Easy Full-Text Search Queries.


Last edited Jul 4, 2012 at 7:42 PM by scwebgroup, version 4