Searching on SQLTeam.com

By Bill Graziano on 2 January 2001 | Tags: Application Design

Many web sites like this one give each article their own web page. My site stores each article as a field in the database. This article shows you how I wrote a search function that ranks the results.

If you take a quick look at my search page, you'll see that you can enter keywords and it will search the database for those words and rank the results based on where and how it finds those words. (Update: My first choice for searching was full-text indexing. Unfortunately my host doesn't offer that on shared servers. Can't say as I blame them either.)

My first step was to pass the list of words to search to a stored procedure. My ASP code looks like this:

Dim fSearchTerms
fSearchTerms = Request("SearchTerms")

strSQL = "spItemSearch02 '" & fSearchTerms & "', ' '"
Set objRS = objConn.Execute(strSQL)

All the searching is done in the stored procedure spItemSearch02. It receives a series of words separated by spaces and searches for each of those words. The code to parse the word list was covered in this article (The article is mighty short but I'd take a second and review the code).

My articles are stored in a table called Items. I use a view called vActiveItems to display just the published items. I actually put all the user submissions in this table also. Some of the important fields are:

ItemID int  -- (my primary key)
Title char(100)
Abstract varchar(4000)
Story text
DatePosted datetime

The first part of the stored procedure creates a temporary table. I'll use this table to hold the ItemID's of the records that the search finds.

Create procedure spItemSearch02
( @Array varchar(1000),
@separator char(1) ) 
AS
-- Created by graz@sqlteam.com
set nocount on
-- @Array is the array we wish to parse
-- @Separator is the separator charactor such as a comma
declare @separator_position int -- Locates each separator character
declare @array_value varchar(1000) -- Holds each returned array value
declare @like_text varchar (1000)

-- Build my Temp Table to hold results
CREATE TABLE #SearchResults (ItemID int)

Next I'm going to loop through each word passed in to the procedure:

-- For my loop to work I need an extra separator at the end.  I always look to the
-- left of the separator character for each array value
set @array = @array + @separator

-- Loop through the string searching for separtor characters
while patindex('%' + @separator + '%' , @array) <> 0 
begin

  -- patindex matches the a pattern against a string
  select @separator_position =  patindex('%' + @separator + '%' , @array)
  select @array_value = left(@array, @separator_position - 1)

  select @like_text = '%' + @array_value + '%'

The variable @like_text holds the word I'm searching for each time I go through the loop. It also includes the wild card character (a percent sign) at the start and end of the string. I search the Title, Abstract and Story fields separately. That code looks like this:

  INSERT #SearchResults
  SELECT ItemID
  FROM vActiveItems
  WHERE Title like @like_text

  INSERT #SearchResults
  SELECT ItemID
  FROM vActiveItems
  WHERE Abstract like @like_text

  INSERT #SearchResults
  SELECT ItemID
  FROM vActiveItems
  WHERE Story like @like_text

  -- This replaces what we just processed with an empty string
  select @array = stuff(@array, 1, @separator_position, '')
end

That snippet shows my three INSERT/SELECT statements and the code to finish the loop. If a given word appears only in the Title, it's ItemID will only be in the #SearchResults table once. If it appears in two of the searches, the ItemID will be in the table twice. If I'm searching on two words and they both appear in the title, the ItemID will be placed in the #SearchResults table twice. This is how I do the rankings of the search results. Last is my statement to return the results to the calling ASP page:

SELECT  TOP 50 S.ItemID, I.Title, 
        DatePosted=Convert(varchar, I.DatePosted, 101), 
        I.URL, Rating=Count(*)
FROM	#SearchResults S,
	vActiveItems I
WHERE	S.ItemID = I.ItemID
Group by S.ItemID, I.Title, I.DatePosted, I.URL
Order by 5 DESC, 3 DESC

set nocount off

GO

I use a GROUP BY clause and order the results by the count of times each item appears in the result set. I use the ordinal position of the fields to ORDER BY rather than the field names. This makes it easier to sort when you are using a GROUP BY clause. I also limit my search to 50 items.

I'd like to enhance this code to weight the different searches. For example, finding a word in the Title should be worth more than finding it in the article body. You could also enhance this to make the number of stories dynamic or to page the result sets. But that's a project for another day. You can see the complete text of the stored procedure here.