SQL Server™ 2005 Bible

Chapter 13. Using Full-Text Search

In This Chapter

• Setting up full-text index catalogs with Management Studio or T-SQL code

• Maintaining full-text indexes

• Using full-text indexes in queries

• Performing fuzzy word searches

• Searching text stored in binary objects

• Full-text search performance

Several years ago I wrote a word search for a large database of legal texts. For word searches, the system parsed all the documents and built a word-frequency table as a many-to-many association between the word table and the document table. It worked well, and word searches became lightning-fast. While I found coding the string manipulation fun, fortunately, you have a choice.

The server versions of Windows include a structured word/phrase indexing system called MS Search. More than just a word parser, MS Full Text Search Engine actually performs linguistic analysis by determining base words and word boundaries, and conjugating verbs for different languages. SQL Server leverages MS Full Text Search Engine on a row and column basis as full-text search catalogs.

ANSI Standard SQL uses the like operator to perform basic word searches and even wildcard searches. For example, the following code uses the like operator to query the Aesop's Fables sample database:

-- SQL Where Like
SELECT Title
  FROM Fable
  WHERE Fabletext LIKE '%lion%'
    AND Fabletext LIKE '%bold%'

Result:

Title
----------------------------------------------
The Hunter and the Woodman

Note

All the code samples in this chapter use the Aesop's Fables sample ...