Full text search - problem with query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Full text search - problem with query

Author

Topic

matthisco
Starting Member

48 Posts

Posted - 2010-06-30 : 08:38:18

Hi,

I've setup my full text catalog in the database I want to search.

I've added the tables to the catalog and used their PK as the catalog index.

I'd like to search the following fields for a text string, from the 2 tables below:

pages.pageBody, pages.pageSummary, pages.pagetitle, sites.siteName

Can anyone please tell me the sql for this, and how to rank the results using a percentage?

Many thanks

pages:
PK pageID int Unchecked
siteID int Checked
pageLink nvarchar(MAX) Checked
pageBody ntext Checked
pageSummary ntext Checked
pageTitle nvarchar(MAX) Checked
pageOrder int Checked
pagehome bit Checked
pageadded smalldatetime Checked
pageLive bit Checked

sites:
PK siteID int Unchecked
siteName nvarchar(MAX) Checked
siteadmin nvarchar(MAX) Checked
siteLive bit Checked

matthisco
Starting Member

48 Posts

Posted - 2010-07-01 : 05:10:04

Any ideas folks?

I have 2 tables I'm like to do a ranked free text search on. So far I have this query off the web, how do I use the full text catalog in all this?

SELECT f.rank, note_id, note_text
FROM productnotes
FREETEXTTABLE(full catalog??, fulltext catalog column????, 'searchterm') f
WHERE productnotes.note_id=f.[key]
ORDER BY rank DESC;

Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources