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.
