Okay, so I have been playing around with Full Text Search in SQL Server 2005. I've worked with it from an administrator's point of view, which is certainly very interesting, but I haven't really gotten to play around with it from a development perspective. Basically, I stuck an index on the body column for this blog, and did a simple FTS query.
Now, to do a query with the functionality of FTS you actually end up using some new keywords:
...
WHERE CONTAINS
(body, @q)
...
WHERE FREETEXT
(body, @q)
...
At first I worked with CONTAINS, which takes the same parameters for the most part. While it worked, it would struggle with multiple words in a query. Second I tried FREETEXT, which is more friendly to multiple search keywords.
This takes care of a simple search on the body of the blog article, but what about if I'm searching for something in a title? There are lots of ways to do it, some much better than others. Here is the simplest query I could come up with. It is neither designed to be fast, nor as accurate is possible. It is designed for simplicity. It could very easily be reworked, and if anyone has some ideas as to what could work, let me know! I'd like to see what other people would do.
CREATE PROCEDURE [dbo].[DoSearch]
@q nvarchar(4000)
AS
BEGIN
SET NOCOUNT ON;
SELECT [PostID],
[Title],
[Body] AS PostSnippet,
[EntryDate] AS PostDate
FROM [dbo].[sb_posts]
WHERE FREETEXT
(body, @q)
OR [Title] LIKE '%' + @q + '%'
;
END
Thoughts? Comments? Questions? Give'r!
Tags:
Written by Steve Syfuhs on
11/13/2008 12:53:47 PM
| Views:
980