SiteCrafting Blah Blah Blog

Nov. 3, 2006 at 10:51am

How To Full-Text Search

Inspired by Joe's MySQL Cross Table Content Search


Here's a quick how-to on implementing Full-Text Searching using Microsoft SQL Server 2005. Originally, I planned on just using LIKE statements in the WHERE clause of an sql query, however, this would not be possible since, as I posted earlier in MS SQL Server 2005 text and ntext, that string functions do not work on text and ntext data types.

That's when I remembered Joe's blog entry about Cross Table Content Search, which he also mentioned in the office a couple of times before his entry. After that, I've been wanting to implement the Cross Table Content Search, and did not get the opportunity until developing the search page for the Pierce County Library.
So, I delved into applying Cross Table Content Search and began researching ways to do that for Microsoft Server 2005. There are basically three parts, adding full-text to the database, creating a stored procedure, and creating a dataset to use that stored procedure.

Add Full-Text Indexing

The very first step is ensuring that full-text indexing has been enabled for the database. The instructions I read online mentioned that this should be enabled when a new database is created, but I found this not to be the case for both our local testing server and the live server.

These are steps to use when using SQL Server Management Studio for Microsoft Server 2005.
  1. Enable Full-text. The link is a how to, please note that the "Use full-text indexing" is a check-box near the top.
  2. Enable A Table for Full Text Indexing.
    1. Following these will cause a wizard to start.
    2. The first part is selecting which fields from the table should be indexed.
    3. The second part is creating a catalog or using an existing catalog. You can give a new catalog any name you wish and all the indexed fields will be added to this catalog.
    4. There's also options for creating a job that runs to update the catalog at a certain date and time, but this can be ignored if you chose to update the catalog automatically.
    5. After the wizard completes, right-click the table and then click Full-Text Indexing->Start Full Population.
    6. The article Understanding SQL Server Full-text Indexing explains the wizard for SQL Server 200. Please look under the heading Enabling Full Text Indexing.
  3. Continue to enable each table that you wish to have Full-Text Indexing.

Create a Stored Procedure

Initially, I intended to simply create a query in an asp.net 2.0 dataset and use a parameter for the search term. However, I quickly learned that queries and views can't use parameters when using the FREETEXT() and CONTAINS() predicates. Meaning Select * from kewl_gadgets Where CONTAINS(description, @search_terms) would fail. However, the parameter will work for a stored procedure.

Here's a sample stored procedure:

USE [kewlDatabase]

GO

/****** Object:  StoredProcedure [dbo].[spKewlSearch]    Script Date: 11/01/2006 17:18:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Ken Foubert
-- Create date: 11/1/2006
-- Description:    Full Text Search on kewl gadgets content
-- =============================================

CREATE PROCEDURE [dbo].[spKewlSearch]( @searchWords AS nvarchar(255) ) AS  

SELECT kewl_gadgets.name, kewl_gadgets.item_no, kewl_gadgets.cost
FROM kewl_gadets LEFT OUTER JOIN
kewl_gadget_features ON kewl_gadgets.gadget_number = kewl_gadgent_features.gadget_number
WHERE CONTAINS( (kewl_gadgets.name, kewl_gadgets.description), @searchWords)
OR CONTAINS(kewl_gadget_features.*, @searchWords)

return
There are two ways to tell which fields that CONTAINS() should look at.
  1. List field names from a table that should be searched on. This list needs to have parenthesis and the fields need to be full-text indexed, ie (kewl_gadgets.name, kewl_gadgets.description)
  2. Use table_name.* to tell CONTAINS()  to do a search on all the fields that have been full-text indexed.
  3. Create a CONTAINS() predicate for each table you wish to search on.
NOTE: When passing the @searchTerm values, please make sure that the words have quotes around them when using the CONTAINS(). @searchTerm = "kewl radios". You can parse out the search term to be @searchTerm = "kewl" and "radios" or @searchTerm = "kewl" near "radios", etc.

This stored procedure is good for simple searches. You have an option of also using FREETEXT() which looks for words that are similar to the search terms by using a thesaurus.

To learn more click CONTAINS or FREETEXT.

In addition, with Full-text indexing you can get results that return a relevancy number by using CONTAINSTABLE or FREETEXTTABLE. For additional information take a look at Full-Text Search Developer InfoCenter.

Create Dataset

The final step is to create a dataset or data adapter to use the stored procedure. I will not go into detail on how to do this. I leave that up to you.

Posted in ASP.NET 2.0, From the Workbench, MS SQL Server by Ken Foubert

Comments (0)

Add your comment below


Remember me
Name: Email: URL: Comment: *   No HTML, http:// will auto-link
* required    Comment Guidelines