Large Keyword Search

I'm working on a project that will allow a user to search through approx 100,000 records in a SQL table. Three of the columns are 'text' fields that hold paragraphs of text. The user interface has a 'general search' option so that they can enter a number of key words and the database will return a count of the records found containing the keywords.

At the moment I split the input and then build a query based on their input. For instance if they enter 'hello world' the input is split into two strings 'hello' and 'world'. I then build the query in a loop and get a query like so:

Select Count(ID) as myCount FROM myTable WHERE (colOne like '%hello%' AND colOne like '%world%') OR (colTwo like '%hello%' AND colTwo like '%world%') OR (colThree like '%hello%' AND colThree like '%world%')

Unfortunately this query runs EXTREMELY slowly and just seems wrong. Is there a more efficient way I should be doing these types of searching? This method works ok on 100 records, but this is the first time I have worked on such a large database.

Is it also possible to search a text column and look for exact matches?

For instance I have 2 records with their textfield containing:

Rec 1: the news for today is blah blah.
Rec 1: this is a new item

If I currently search for 'new' (select colID from myTable where colOne like '%new%') I will get both these records, but I'd really only like to pull out the second record.

Any help would be great appreciated! :)

You could use Full Text and use Microsoft proprietry CONTAINS, FREETEXT and CONTAINSTABLE and FREETEXTTABLE. The former are predicate and the later are row functions. They are dependent on Full Text index which require the Microsoft Search Service to be populated. Hope this helps.

Sample

SELECT product_id, product_name, From products
WHERE FREETEXT (description, 'manage')

SELECT product_id, product_name, From products
WHERE CONTAINS (description, ' "config" ')

Kind regards,

Gift Peddie