Full-Text Search Engine

Following is the content table:

 Create table content 

(

 Pagename varchar(20) not null primary key,

 URL varchar(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.