Ignored words in the full text query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Griff - 13 Mar 2006 17:20 GMT

Got a bizarre problem

In our search box, someone types in the search term:
A$

This results in the PARTIAL SQL:
......AND (CONTAINS(TableToQuery, ' ""A$"" '))

And this results in the error:
Server: Msg 7619, Level 16, State 1, Line 1
Execution of a full-text operation failed. A clause of the query
contained only ignored words.

The set of "noise" words:
C:\Program Files\Microsoft SQL
Server\MSSQL\FTDATA\SQLServer\Config\noise.eng

has two lines that appear relevant:
$
a b c d e f g h i j k l m n o p q r s t u v w x y z

But the "word" being searched is "A$" and not "A" with "$".

Can someone explain why I'm getting this particular error?

Thanks in advance

Griff

PS - Using SQLServer 2000 on Windows 2003 being queried from an ASP script
running in IIS under Windows 2000. I know that the "word delimiters" list
is different on Windows2000 & Windows 2003, but I don't know if there are
any other server- related issues.

Reply to this Message

Hilary Cotter - 14 Mar 2006 12:00 GMT

You need to remove the a, so the lines would look like
$
bcdef.....

The $ is thrown away so a search on a, a$, or $a would give hits to a, a$,
$a, !a, a!, etc.

After you make your change to the noise word list - for US english noise.enu
found in

C:\Program Files\Microsoft SQL Server\MSSQL$SQL2000\FTDATA\SQLServer\Config
stop mssearch, save your changes, restart mssearch and do a full population.

Signature

Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

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

> Got a bizarre problem
>
[quoted text clipped - 29 lines]
> is different on Windows2000 & Windows 2003, but I don't know if there are
> any other server- related issues.

Reply to this Message

Griff - 14 Mar 2006 13:16 GMT

Hi Hilary

Many thanks for your response. I have a few further questions just to help
clarify my understanding.

You say that the $ symbol is "thrown away". What are the exact criteria for
throwing these characters away? I.e., are they ALWAYS ditched, only ditched
if on the end of a word...etc?

Presumably the $ character is ditched when the data in the table field is
actually indexed. As an example, say that I had the following text in the
table "abcd$efgh". How would this be saved in the full text index: as a
single word "abcdefgh" or as two words "abcd" and "efgh"? If I wanted to
enter a search term that would return this row, I'd presumably have to enter
the indexed word (so either "abcdefgh" if one word or either "abcd" or
"efgh" if two words); entering "abcd$efgh" presumably would return no hits.

If $ is a "throw away" character, what other characters are also thrown
away? The reason I'm interested in this is that I want to pre-parse the
search terms that are entered to see if they are suitable for use within a
full-text query. So, I'd need to throw away all $ characters (and all the
other characters that are also known to be thrown away when the full text
index is searched) and see what was left. Is this simply the list in the
noise.eng file (UK English)? Are the criteria for throwing these characters
away the same for all characters? I assume that they must be.

Many thanks

Griff

Reply to this Message

Hilary Cotter - 16 Mar 2006 14:21 GMT

All non alphanumeric characters are thrown away and replaced by white space.
So a search on A will match with a!, !a, a@, @a, a#, a$, a%, "a.", "a,",
etc. A search on a! will match with A!, a., a, a$, (A, etc. Note that its
case insensitive and this behavior applies to tokens of more than one
letter, ie !aa. Note further that a must be removed from the noise word list
for this to work.

There are some exceptions.

`,_, following any letter whether the word/letter is in the noise word list
of not will result in a new token.
So that a search on `B, or `b, will match in a case insensitive manner with
b` or `B, and the same with _. If your column has b, B, `b, and B` a search
on b, will return b and B (if they are not in the noise word list), and a
search on `b will match only with `b or `B whether b is in the noise word
list or not.

. ++ following lower cased letters, and sharp # following upper cased
letters. A search c++ will match with c++ and not with c. c does not have to
be in the noise word list for this to work. And its any letter of the
alphabet, but not numbers.

HTH - I have submitted a paper for publication on this matter to
simple-talk. It was supposed to have been published in Feb.

Signature

> Hi Hilary
>
[quoted text clipped - 26 lines]
>
> Griff

Reply to this Message