SQL Server Full Text

[rsshetty]

I just added The SQL Server 2000 Full Text search capability to a table in a database. While trying out search keywords, I noticed a problem.
When I give,
select * from message where freetext(*,'daytime')

There are no results returned.

However, when I give,
select * from message where msg_body like '%daytime%'

I get one record. I have added msg_body to the index. Am I missing something here?

rsshetty.
It's always in the details.

 

[mrdenny]

Try using the Contains or ContainsTable Keyworks.

Full-text queries using FREETEXT are less precise than those full-text queries using CONTAINS. The Microsoft® SQL Server™ full-text search engine identifies important words and phrases. No special meaning is given to any of the reserved keywords or wildcard characters that typically have meaning when specified in the <contains_search_condition> parameter of the CONTAINS predicate.

SELECT *
FROM Message
WHERE CONTAINS(Msg_Body, '"daytime"')

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[rsshetty]

Sorry. My mistake. I should've given a wildcard - daytime*

The word that matches it is "daytimer".

I stumbled on something else though:

select* from message where contains(*,'"people here"')

returns 183 records
whereas,

select * from message where msg_body like '%people here%'

returns none.

I am looking for a specific phrase - "people here"

rsshetty.
It's always in the details.

 

[rsshetty]

Does anyone know how a phrase is handled by SQL Server contains operator?

doe sit search for the exact phrase only or does it break it down to individual words and seek them out as well?

rsshetty.
It's always in the details.

 

[mrdenny]

It depends on how you pass the data. '"people here"' will be handeld differently that '"people" "here"'.

Check BOL under Contains.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)