SQL full-text-search

Question

I have set up the full-text search functionality in SQL Server 2008 express. This is what I did:

-- STEP 1: Create catalog
create fulltext catalog HtmlSearch

-- STEP 2: Fill catalog
create fulltext index on docs
(WordHtml)
key index IX_docs_1
on HtmlSearch
with change_tracking auto

-- STEP 3: Search
select * from docs
where freetext(*, 'beleid')

The table docs has one row where the field WordHtml has (html)content. When I execute step 3, nothing happens. I am sure the term 'beleid' exists in the html-document. The result of the query is 0 records. So what am I doing wrong? Do I somewhere have to configure that this field is Html? And of so, how do I do this?

Answer 1

A similar query worked on a database I have, so I'd look at the setup. Here are some things you can try:

The index doesn't seem to be doing you any good right now anyway, so I'd drop it and try recreating it. I'm only familiar with 2005, but the syntax you used doesn't look like what I have. You could try (after the appropriate drops)

CREATE FULLTEXT Catalog HtmlSearch AS DEFAULT;

GO

CREATE FULLTEXT INDEX ON docs ( WordHtml Language English ) KEY INDEX IX_docs_1 WITH CHANGE_TRACKING auto;

GO

I assume WordHtml is the name of the column you want to index. If that's the case, then this matches what I did in 2005.

Next you can try explicitly populating the full text index. This might not be necessary, but I always do it after I create a full text index.

ALTER FULLTEXT INDEX ON docs START FULL POPULATION;

GO