Okay I have finally found full text-searching and got it working with SQL2000.
Now it appears to be working correctly however I am having difficulty getting to grips with FREETEXTTABLE, and help/advice would be appreciated.
Now this is what I would like to do. One form field on a web page, passes over a search phrase which then want to use to search across all rows of my selected table and then return WEIGHTED results or RANKED results.
Now I have used CONTAINS etc and had fairly good results however assuming what I am reading via MSDN using FREETEXTTABLE will give me much better and fairly accurate results based on a whole search phrase entered by a user.
Incidentally should I use CONTAINSTABLE?
Quick Note: I am only wanting to search through one table, no more than 2000 rows.
So I have my table (lets call it Account_List) containing the following rows:
I have a populated full-text catalog for the above table containing all of the rows.
Now what I want to do is say for example the user inputs the phrase 'Argos in Milton Keynes' I would like it to go an search each row and return weighted results based on that. In this case the key columns are AccountName and AccountTown.
Now the only example so far I have is:
FROM Categories AS FT_TBL INNER JOIN
'sweetest candy bread and dry meat') AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
Microsoft standard example, however I don't get why I would have to do an INNER JOIN, what am I missing here? Like said before I am only using one table.
I would like a example of a simple string to search my database from the user input form.
Queries that use the CONTAINSTABLE and FREETEXTTABLE functions are more complex than those that use the CONTAINS and FREETEXT predicates because qualifying rows returned by the functions must be explicitly joined with the rows in the original SQL Server table.