Best way to do a free text search

HiI have a .net 2 website that works from a sql 2000 db. I am building a form that will allow the user to type in any search criteria and based on their input I need to produce some results (that's the simplest way to put it)I will need to search various parts of my db to find similar sounding information, I was just wondering what is the best way to do this. I had the following thoughts1) Search columns using Soundex tsql function (but not sure how good this is?)2) Remove all noise words from user input (eg, and, or, the etc...) and then use a regular expression to search the fields in the dbAlternatively are their some third party components to do can do this for me Many thanks in advance

You can full text search enable the database, and create a full text index on the column in the table you want to search. Then you will be able to search any word that appears inside that column. Here are a couple of links that might be helpful to you.

http://www.databasejournal.com/features/mssql/article.php/3441981

http://www.wherecanibuyit.co.uk/ASP/full-text-search.html

http://msdn2.microsoft.com/en-us/library/ms177652.aspx

|||

Hi

Thanks for that, it was a great help

I have noticed that their is a RowNumber method in SQL 2005, is their any way to do this functionality in SQL 2000

One method I thought of was to put the data in temp table and loop through each record and give it a number, and then pull out 10 records at a time as the user pages through.

Are their any other ways I could do this.

Many thanks in advance

|||

The short answer is yes. You may want to search for articles on Custom Paging with SQL Server 2000. There are ways to do it, but they are not as simple or clean as the ROW_NUMBER() function. Here is an article to get you started.

http://www.4guysfromrolla.com/webtech/042606-1.shtml