Hello everyone,

I use full text search using containstable for search on my intranet
site.  Its been working wonderfully.  However, I have recently been
working on an upgrade to my search page to allow users to exclude
words.  When excluding words I use the "and not" operator.  I have
noticed that with some words it works, and with others it does not.
None of my words are noise or ignored words.

The below query returns 6 results (not using the excludes):

Select FT_TBL.UID as ID, FID, Category, Link, target, Title, SubTitle,
Description, LastUpdate, LU_SearchCategories.TypeName,
LU_SearchCategories.TypeShort, KEY_TBL.RANK FROM ICDB.dbo.SearchTable
FT_TBL INNER JOIN CONTAINSTABLE(ICDB.dbo.SearchTable, *, '( "rte*" )
AND ( "billing*" ) AND ( "opt*" ) AND ( "editor*" )') KEY_TBL ON
FT_TBL.UID = KEY_TBL.[KEY] INNER JOIN ICDB.dbo.LU_SearchCategories
LU_SearchCategories ON FT_TBL.Category = LU_SearchCategories.TypeID
WHERE FT_TBL.PermID <= (Select Users.Role from InfoCenter.dbo.Users
Users where Users.UID = 5432) and Category in (2,3) ORDER BY
KEY_TBL.RANK DESC

The top results in the query above returns a record that also contain
the words calculations and also the word integer.  When I exclude
either of these words....it doesn't exclude that results from the
results.

Example using "and not"

Select FT_TBL.UID as ID, FID, Category, Link, target, Title, SubTitle,
Description, LastUpdate, LU_SearchCategories.TypeName,
LU_SearchCategories.TypeShort, KEY_TBL.RANK FROM ICDB.dbo.SearchTable
FT_TBL INNER JOIN CONTAINSTABLE(ICDB.dbo.SearchTable, *, '(( "rte*" )
AND ( "billing*" ) AND ( "opt*" ) AND ( "editor*" )) and NOT (
"calculations*" )') KEY_TBL ON FT_TBL.UID = KEY_TBL.[KEY] INNER JOIN
ICDB.dbo.LU_SearchCategories LU_SearchCategories ON FT_TBL.Category =
LU_SearchCategories.TypeID WHERE FT_TBL.PermID <= (Select Users.Role
from ICDB.dbo.Users Users where Users.UID = 5432) and Category in (2,3)
ORDER BY KEY_TBL.RANK DESC

Further...when I include the word "calculations" in the search query as
a required word...it doesn't pull the record...actually..it doesn't
pull any records.
Example query:

Select FT_TBL.UID as ID, FID, Category, Link, target, Title, SubTitle,
Description, LastUpdate, LU_SearchCategories.TypeName,
LU_SearchCategories.TypeShort, KEY_TBL.RANK FROM ICDB.dbo.SearchTable
FT_TBL INNER JOIN CONTAINSTABLE(ICDB.dbo.SearchTable, *, '( "rte*" )
AND ( "billing*" ) AND ( "opt*" ) AND ( "editor*" ) AND (
"calculations*" )') KEY_TBL ON FT_TBL.UID = KEY_TBL.[KEY] INNER JOIN
ICDB.dbo.LU_SearchCategories LU_SearchCategories ON FT_TBL.Category =
LU_SearchCategories.TypeID WHERE FT_TBL.PermID <= (Select Users.Role
from InfoCenter.dbo.Users Users where Users.UID = 5432) and Category in
(2,3) ORDER BY KEY_TBL.RANK DESC

The words "integer" and "calculations" are not the only words it does
this on...there are others.

Of course as I stated previously...some words to accurately exclude
those results...as in this case with the word "clmfmtdta".  Query
example below.

Select FT_TBL.UID as ID, FID, Category, Link, target, Title, SubTitle,
Description, LastUpdate, LU_SearchCategories.TypeName,
LU_SearchCategories.TypeShort, KEY_TBL.RANK FROM ICDB.dbo.SearchTable
FT_TBL INNER JOIN CONTAINSTABLE(ICDB.dbo.SearchTable, *, '(( "rte*" )
AND ( "billing*" ) AND ( "opt*" ) AND ( "editor*" )) and NOT (
"clmfmtdta*" )') KEY_TBL ON FT_TBL.UID = KEY_TBL.[KEY] INNER JOIN
ICDB.dbo.LU_SearchCategories LU_SearchCategories ON FT_TBL.Category =
LU_SearchCategories.TypeID WHERE FT_TBL.PermID <= (Select Users.Role
from InfoCenter.dbo.Users Users where Users.UID = 5432) and Category in
(2,3) ORDER BY KEY_TBL.RANK DESC

Does anybody have any ideas as to why this is doing this?  Or maybe a
better way to use the "and not" operator?