Sample Full-Text Search Engine

Suppose you want a table to hold all content for your dynamically populated Web site. In the following example, I'll create a simple SQL Server table and a full-text search engine to run against it.

Related: Build a Better Search Engine and Do-It-Yourself Searching

Following is the content table:

Create table content (

Pagenamevarchar(20) not null primary key,URLarchar(30) not null,Descriptiontext null,Keywordsvarchar(4000) null)

Insert the following values.

INSERT content values ("home.asp","home.asp","This is the home page","home,SQL")GOINSERT content values ("pagetwo.asp","/page2/pagetwo.asp","NT Magazine is great","second")GOINSERT content values ("pagethree.asp","/page3/pagethree.asp","SQL Magazine is the greatest","third")GO 

The table will look like this:

Next, review the full-text enabling procedures, enable all columns for full-text searching, and populate the index.

Let's practice querying this table using some keyword searches. Open SQL Server 7.0's Query Analyzer, and try some of the following queries.

Query 1 (FREETEXT)

SELECT * FROM content WHERE freetext(*,"home") 

This queries all full-text-enabled columns in the content table for the string "home."

Query 2 (FREETEXT)

SELECT * FROM content WHERE freetext(description,"Magazine")

This only searches the Description column and returns all matches for the string "Magazine."

Query 3 (FREETEXT)

SELECT * FROM content WHERE freetext(description,"SQL Mag")

Although this appears to search on the string "SQL Mag," it actually searches on "SQL" or "Mag."

Query 4 (FREETEXT)

SELECT * FROM content WHERE freetext(description,"the")

Result

Server: Msg 7619, Level 16, State 1, Line 1

The query contains only ignored words; we've queried a noise word here. You'll find "the" in the noise words file at MSSQL7FTDATASQLSERVERCONFIG.

Query 5 (CONTAINS)

SELECT * FROM content WHERE contains(*,"home")

Like the Freetext query, this searches all full-text-enabled columns for the keyword "home."

Query 6 (CONTAINS)

SELECT * FROM content WHERE contains(Description,'  "Magaz*"  ')

This statement queries the Description column for a word beginning with "Magaz." Note that the asterisk acts as a wildcard or placeholder, just as the percent sign (%) does with the LIKE keyword. (To make this work, you need to use single quotes on either side of the double quotes.)

Query 7 (CONTAINS)

SELECT * FROM content WHERE contains(Description,'  "*azine"  ')

This search yields no results. You can't use an asterisk as a placeholder for a prefix.

Query 8 (CONTAINS)

SELECT * FROM content WHERE contains(Description,'  "Magazine" Or "Great"  ')

This full-text scan uses OR so that you can search for "Magazine" or "Great"; it also works with AND and AND NOT. (Again, note the single quotes around the search criteria.)

Query 9 (CONTAINS)

SELECT * FROM content WHERE CONTAINS(description, 'NT NEAR great')

This search on the Description column finds all rows where "NT" is near "great".

Query 10 (CONTAINS)

SELECT * FROM content WHERE contains(description, ' formsof (inflectional, great) ')

This statement returns all results for "great," "greatest," "greater," and so on.