funny search results with 'contains' clause

hi,
it is really funny how ft search works:
it seems to depend highly on what could be indexed, but it still doesn't
follow a determined order.
consider the following queries:
select name from person where contains(name, 'hammer')
select name from person where contains(name, '"hammer*"')
the first is a query with a "simple term" and the second one with a
"prefix term".
mssql docu says that a simple term "specifies a match for an exact word",
but the first query returns other matches like "oberhammer" or
"hammerschmied" - this is not an exact match for me; the found words
rather consist of two words "ober", "schmied" and "hammer".
one would expect that the 1st and 2nd query work absolutely different but,
even the 2nd returns a lot more results, they seem to differ just slightly.
when searching for 'ober' (instead of 'hammer'), the 2nd query returns
"obermayr" and "oberlehner", but the 1st results only in "oberlehner",
although "ober", "mayr" and "lehner" are single words themselves.
and why does it find "Tober" or "Dober" if i only searched for 'ober'?
i need to know the rules which mssql uses for fulltext search. can someone
point me to a book or a website where i can find more information? or does
somebody have a good explanation?
btw, we use sql2000 and a german collation.
klaus triendl
unfortunately this behavior is caused by the German wordbreaker. You might
get better results with the neutral word breaker.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"klaus triendl" <triendl.kj@.m-box.at> wrote in message
news:opsmm8g1r7cm4gx4@.dev4xp.wasser.local...
> hi,
> it is really funny how ft search works:
> it seems to depend highly on what could be indexed, but it still doesn't
> follow a determined order.
> consider the following queries:
> select name from person where contains(name, 'hammer')
> select name from person where contains(name, '"hammer*"')
> the first is a query with a "simple term" and the second one with a
> "prefix term".
> mssql docu says that a simple term "specifies a match for an exact word",
> but the first query returns other matches like "oberhammer" or
> "hammerschmied" - this is not an exact match for me; the found words
> rather consist of two words "ober", "schmied" and "hammer".
> one would expect that the 1st and 2nd query work absolutely different but,
> even the 2nd returns a lot more results, they seem to differ just
slightly.
> when searching for 'ober' (instead of 'hammer'), the 2nd query returns
> "obermayr" and "oberlehner", but the 1st results only in "oberlehner",
> although "ober", "mayr" and "lehner" are single words themselves.
> and why does it find "Tober" or "Dober" if i only searched for 'ober'?
>
> i need to know the rules which mssql uses for fulltext search. can someone
> point me to a book or a website where i can find more information? or does
> somebody have a good explanation?
> btw, we use sql2000 and a german collation.
>
> --
> klaus triendl
|||klaus,
Could you post the full output of the below SQL code as it is most helpful
in troubleshooting SQL FTS issues.
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
sp_configure 'default full-text language'
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
go
Specifically, the OS-supplied and language-specific wordbreaker along with
the actual text (single language or mixed?) you are storing in your
FT-enabled table's column is most important information!
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"klaus triendl" <triendl.kj@.m-box.at> wrote in message
news:opsmm8g1r7cm4gx4@.dev4xp.wasser.local...
> hi,
> it is really funny how ft search works:
> it seems to depend highly on what could be indexed, but it still doesn't
> follow a determined order.
> consider the following queries:
> select name from person where contains(name, 'hammer')
> select name from person where contains(name, '"hammer*"')
> the first is a query with a "simple term" and the second one with a
> "prefix term".
> mssql docu says that a simple term "specifies a match for an exact word",
> but the first query returns other matches like "oberhammer" or
> "hammerschmied" - this is not an exact match for me; the found words
> rather consist of two words "ober", "schmied" and "hammer".
> one would expect that the 1st and 2nd query work absolutely different but,
> even the 2nd returns a lot more results, they seem to differ just
slightly.
> when searching for 'ober' (instead of 'hammer'), the 2nd query returns
> "obermayr" and "oberlehner", but the 1st results only in "oberlehner",
> although "ober", "mayr" and "lehner" are single words themselves.
> and why does it find "Tober" or "Dober" if i only searched for 'ober'?
>
> i need to know the rules which mssql uses for fulltext search. can someone
> point me to a book or a website where i can find more information? or does
> somebody have a good explanation?
> btw, we use sql2000 and a german collation.
>
> --
> klaus triendl
|||I am raising a support incident on this one Klaus.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u3I$G3ZGFHA.4004@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> unfortunately this behavior is caused by the German wordbreaker. You might
> get better results with the neutral word breaker.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "klaus triendl" <triendl.kj@.m-box.at> wrote in message
> news:opsmm8g1r7cm4gx4@.dev4xp.wasser.local...
word",[vbcol=seagreen]
but,[vbcol=seagreen]
> slightly.
someone[vbcol=seagreen]
does
>