Searching on SQLTeam.com
By Bill Graziano
on 2 January 2001
| 12 Comments
| 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:
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)
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) )
-- Created by email@example.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
-- 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 + '%'
@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:
WHERE Title like @like_text
WHERE Abstract like @like_text
WHERE Story like @like_text
-- This replaces what we just processed with an empty string
select @array = stuff(@array, 1, @separator_position, '')
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),
FROM #SearchResults S,
WHERE S.ItemID = I.ItemID
Group by S.ItemID, I.Title, I.DatePosted, I.URL
Order by 5 DESC, 3 DESC
set nocount off
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.