Keyword Search Engine

[david7777777777]

I've built a SQL 2000 database I named KnowledgeBase. I'm building ASP pages using VisualInterDev 6 to make a search page. I've supplied a textbox (txtKeywords) for the user to type keywords into (separated by a comma if there is more than 1 keyword).

After reading SQL-BOL about and enabling Full Text Indexing on the database, I can still not figure out the appropriate syntax to use to make sure the keyword I'm entering is actually being used in the correct way by SQL server. It keeps coming up with no results found. Any clues will be appreciated. Thanks.

 

[david7777777777]

No one wants to touch this one huh? ;-)

 

[MerlinB]

We use full text indexing, and it is usually OK. Make sure the index is fully populated!

The sql will be different if you want to sort/extract the article 'rank', to one where you are not bothered with the rank:

Without Ranking

SELECT KB.* FROM KB WHERE CONTAINS (*, '"A*" or "B*"')

With Ranking

SELECT KB.*, KEYS.RANK
FROM KB INNER JOIN
CONTAINSTABLE(KB, *, '"A*" or "B*"') AS KEYS
ON KB.KeyCol = KEYS.[KEY]
ORDER BY KEYS.RANK

Where KB is your knowledge base table name, and KB.KeyCol is the name of its primary key column.

Now the hardest part is re-defining the text as entered by your users as a suitable 'Contains' search term (shown as '"A*" or "B*"' above). You will need to put quotes around each word the user types, with a star * after each word. You will need to replace the commas (and/or spaces) with OR or AND as appropriate. Make sure any single quotes in any word are doubled up (or removed). Then plonk the results in the Contains/Containstable clause as a single-quoted string.
eg
sun, sand and sea
may get converted to
'"sun*" or "sand and sea*"'
or to
'"sun*" or ("sand*" and "sea*&quot'
or whatever.

I have not managed to get the SQL 'freetext' facility to work correctly.

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)