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 (
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:

 
Pagename URL Description Keywords
-------- --------- --------------------- ---------
home.asp /home.asp This is the home page home, SQL
Pagetwo.asp /page2/page2.asp NT Magazine is great second
Pagethree.asp /page3/page3.asp SQL Magazine is the greatest third

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")
 
Result
Pagename URL Description Keywords
-------- --------- --------------------- ---------
home.asp /home.asp This is the home page home, SQL

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")
 
Result
Pagename URL Description Keywords
-------- --------- --------------------- ---------
Pagetwo.asp /page2/page2.asp NT Magazine is great second
Pagethree.asp /page3/page3.asp SQL Magazine is the greatest third

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")
 
Result
Pagename URL Description Keywords
-------- --------- --------------------- ---------
Pagethree.asp /page3/page3.asp SQL Magazine is the greatest third

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")
 
Pagename URL Description Keywords
-------- --------- --------------------- ---------
home.asp /home.asp This is the home page home, SQL

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*"  ')
 
Pagename URL Description Keywords
-------- --------- --------------------- ---------
Pagetwo.asp /page2/page2.asp NT Magazine is great second
Pagethree.asp /page3/page3.asp SQL Magazine is the greatest third

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"  ')
 
Pagename URL Description Keywords
-------- --------- --------------------- ---------

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"  ')
 
Pagename URL Description Keywords
-------- --------- --------------------- ---------
Pagetwo.asp /page2/page2.asp NT Magazine is great second
Pagethree.asp /page3/page3.asp SQL Magazine is the greatest third

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')
 
Pagename URL Description Keywords
-------- --------- --------------------- ---------
Pagetwo.asp /page2/page2.asp NT Magazine is great second

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) ')
 
Pagename URL Description Keywords
-------- --------- --------------------- ---------
Pagetwo.asp /page2/page2.asp NT Magazine is great second
Pagethree.asp /page3/page3.asp SQL Magazine is the greatest third

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