Best way to build a search function

Question

I have a website that has over 400,000 items. Some similar, some vastly different. We want to provide a way to search these items the best way possible. After being delivered the website it was using full text indexing. The solution is basic at best, woefully inadequate at worst.

So what is the best way to search these items? They are stored in a SQL Server Database (2005). Our website is designed in C# 2.0.

Currently here is the process:

1. User enters value into text box.
2. We 'clean' this entry. Removing 'scary' characters that could be an attempted hack. Remove key words (and, or, etc..)
3. Pass value into a stored procedure to return results.
4. Return results.

Answer 1

Look at Lucene.NET. I think it's a vast improvement over full-text search in SQL Server.

Answer 2

SQL Server Central has a nice article on creating a Google-like Full Text Search using SQL Server. Unfortunately you have to register view the full article, but registration is free and they post a lot of good information. Here is the link:

http://www.sqlservercentral.com/articles/Full-Text+Search+(2008)/64248/

Excerpt:

...

Google Style

The key to a successful application is to make it easy to use but powerful. Google has done this with their Web search engine. The syntax for queries is simple and intuitive, but full-featured. Though the basic building blocks of a Google query are simple you can combine them in powerful ways. I'll begin with basic Google query syntax and add some additional operators to take advantage of the power of SQL Server CONTAINS predicate syntax. The full Google syntax is defined in the Google Help:Cheat Sheet at http://www.google.com/help/cheatsheet.html.

...

The article has full example code and even a link to download it. Its an interesting read even if you don't plan on implementing it.