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. Following is the content table:

Create table content (

Pagename		varchar(20) not null primary key,
URL			archar(30) not null,
Description		text null,
Keywords		varchar(4000) null)

Insert the following values.

INSERT content values ("home.asp","home.asp","This is the home page","home,SQL")
GO
INSERT content values ("pagetwo.asp","/page2/pagetwo.asp","NT Magazine is great","second")
GO
INSERT 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 \MSSQL7\FTDATA\SQLSERVER\CONFIG.

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.