full text special characters

I am trying to modify my noise word list and found that there are special characters that are ignored no matter what... I need to be able to search on these characters (well at least some of them) so I need to some up with a way to get them into the catalog.

I am running SQL 2000 so using the thesaurus is out (feature not available) and microsoft does not recomment using the thesaurus for special charaters anyways.

SO what I was thinking of was to when I build the data for the catalog, I use replace the special characters with equivalent words. Does this sound like it would work?

Then the procedure that does the searching would also have to look for the special characters and replace them as well....

Does anyone have a better idea?

Also is there any place that I can get the full list of special characters? Here is the one I have.

Apostrophe

'

Accent grave

`

Number sign

#

Percent sign

%

Greater than

>

Less than

<

Exclamation mark

!

Period

.

Brackets

[ or ]

Asterisk

*

Dollar

$

Semicolon

;

Colon

:

Question mark

?

Caret

^

Braces

( or )

Plus sign

+

Backslash

\

Hey, I am unfortunatley unable to provide you with any real feedback reguarding the nature of your problem. However I am a Instructional Technologist designing Aids for users at my current place of work and was hoping you might be able to point me into the right direction as far as the Full-TExt search function goes within SQL.

I need to find out the idiosyncracies of the search so that I can later train on it.

That is, I know that if I search for (arbitrary) form "AD44445" by entering AD I will get a long list of forms that reflect this title,

AD34563

AD42034

AD23404

AD etc etc etc

However I need to know what other pnumonics the search engine relies on to gather the necessary information of the searched item.

If there is a manual that is not technical that tells users why the full text search works the way it does could you please point me in the way of it?

sql

Full Text Search Problem

This is the way FTS works. Look up "noise words" in Books Online. The noise
file (e.g. noise.dat) lists all the words that are ignored when building
full-text indexes, which means searching for them is not possible, hence the
error (which becomes a warning in SQL Server 2005).
You have two options:
1) Handle it in the client application: prevent users from issuing searches
where only the ignored words have been used. You can use the noise file to
programmatically test each search string;
2) Remove the words from the noise list (leave empty lines): this may
increase the space used by full-text catalogs significantly, so only remove
those words that you expect the users to search for.
Perhaps other frequent posters in this newsgroup have other suggestions.
ML
http://milambda.blogspot.com/
Just to piggy back off ML's comment.
I used to recommend stripping the noise words out of your query phrase,
however this will frequently lead to errors, for example a search on
"University Of California" when stripped of its noise word OF, and then the
search conducted on "University California" will miss results containing
"University of California" and "University to California".
IMHO the best approach is to empty your noise word list and replace it with
a single space or as ML points out a line feed.
Note that a FreeText search gets around this problem but may return too many
results and its speed is slower than the Contains.
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
"ML" <ML@.discussions.microsoft.com> wrote in message
news:3A90A3EB-E4BE-4E1F-A8DF-1C6AAD9653E9@.microsoft.com...
> This is the way FTS works. Look up "noise words" in Books Online. The
> noise
> file (e.g. noise.dat) lists all the words that are ignored when building
> full-text indexes, which means searching for them is not possible, hence
> the
> error (which becomes a warning in SQL Server 2005).
> You have two options:
> 1) Handle it in the client application: prevent users from issuing
> searches
> where only the ignored words have been used. You can use the noise file to
> programmatically test each search string;
> 2) Remove the words from the noise list (leave empty lines): this may
> increase the space used by full-text catalogs significantly, so only
> remove
> those words that you expect the users to search for.
> Perhaps other frequent posters in this newsgroup have other suggestions.
>
> ML
> --
> http://milambda.blogspot.com/
|||Yes, noise words aren't all bad, but search strings containing nothing but
noise words are.
ML
http://milambda.blogspot.com/
|||Hi ML, very true and well said.
Historically Noise words were intended to conserve disk space as back in the
80's when search was first starting disks were very expensive. Today they
are intended to "hide" noisy phrases from searching. For example a search on
Microsoft SQL Server is the functional equivalent of a search on SQL Server.
So you get better search efficiency by not looking for Microsoft.
Microsoft (at one time, perhaps still the case) added Microsoft to their
noise word list on their search engines for this reason. Apparently at one
time they also would add words greater than 26 letters to their noise word
list as you would be unable to search on them.
MSN search was one of the first big search engines to allow you to search on
noise words, for example a search on "the" when MSN Search first came out
would return the number on hit to the white house.
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
"ML" <ML@.discussions.microsoft.com> wrote in message
news:90F35431-C496-4273-8C9C-A79B1E9F9D20@.microsoft.com...
> Yes, noise words aren't all bad, but search strings containing nothing but
> noise words are.
>
> ML
> --
> http://milambda.blogspot.com/
|||Thanks for that info - it's essential, a must-know.
Do you by any chance have a list of characters ignored by FTS that aren't
included in noise files (e.g. punctuation marks)?
ML
http://milambda.blogspot.com/
|||Basically all alpha-numeric letters are indexed. Hyphens and capitalization
are respect in some languages. In some languages the indexing process knows
a character occurs after a single letter (i.e. C#), but doesn't index what
the character is, i.e. a search on C# will match with C$.
Currency symbols change how a number is stored in the index as well as
apparent date strings.
Abbreviations are handled differently, for example f.b.i is indexed as f, b,
and i, whereas F.B.I is indexed as FBI, and F.B.I.
IMHO I did an ok job in this article discussing language options in SQL FTS.
http://www.simple-talk.com/sql/learn-sql-server/sql-server-full-text-search-language-features/
If you are really interested in the internals of how this works with most
search engines you might want to look at the code in Lucene or Foundations
of Statistical Natural Language Processing. There is another book which is
really good on this and presents algorithms but I can't recall the name of
it right now.
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
"ML" <ML@.discussions.microsoft.com> wrote in message
news:0B5AC307-1395-4F25-8DF0-4FD2C8A2423C@.microsoft.com...
> Thanks for that info - it's essential, a must-know.
> Do you by any chance have a list of characters ignored by FTS that aren't
> included in noise files (e.g. punctuation marks)?
>
> ML
> --
> http://milambda.blogspot.com/
|||Thank you again! That article is now a permanent reference.
ML
http://milambda.blogspot.com/
sql

Full text query contained only ignored words

Hello All,
Please explain this Fulltext behavior to me.
We have an asp application that is using sql that returns the above error
when you search for the word can't. If you search for "don't" or "Doesn't"
or pretty much anything else with an apostrophe, results are returned.
The same thing happens when you search for a decimal number. If I search
for 4.0, I get the above error. If I search for 4.00.0 I will get results.
Thanks in advance!
Jake
"GitarJake" <spamaintme@.oz.net> wrote in message
news:C0vRe.101151$Ep.81169@.lakeread02...
> Hello All,
> Please explain this Fulltext behavior to me.
> We have an asp application that is using sql that returns the above error
> when you search for the word can't. If you search for "don't" or
> "Doesn't" or pretty much anything else with an apostrophe, results are
> returned.
> The same thing happens when you search for a decimal number. If I search
> for 4.0, I get the above error. If I search for 4.00.0 I will get
> results.
Check the noise.X file in your FTDATA\Config folder (where X is the
extension related to your word breaker language - eng for English, enu for
US English, etc).
4 is in the list. I'm guessing that when 4.0 is passed FTS treats it a
number and so finds it in the noise word file, whereas when 4.00.0 is used
it's obviously not a number and so does a search for it as is.
Can is a word in the noise word list too. I'm guessing FTS splits can't into
can and not internally, and errors because can is in the list.
What you could do is empty the noise word file - leave only a single line in
it with a single space on it. Then run a full population. This has the
effect of not erroring on any words (although some characters that are used
as word breakers will still throw the noise words error, you'll have to
filter these out in your query builder).
Dan
|||You might want to review the following
http://www.indexserverfaq.com/noise.htm
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
"GitarJake" <spamaintme@.oz.net> wrote in message
news:C0vRe.101151$Ep.81169@.lakeread02...
> Hello All,
> Please explain this Fulltext behavior to me.
> We have an asp application that is using sql that returns the above error
> when you search for the word can't. If you search for "don't" or
"Doesn't"
> or pretty much anything else with an apostrophe, results are returned.
> The same thing happens when you search for a decimal number. If I search
> for 4.0, I get the above error. If I search for 4.00.0 I will get
results.
>
> Thanks in advance!
> Jake
>
|||Hi Daniel,
Thanks for responding.
I have removed "can" from both the fulltext noise.enu word list in
ftdata\config; and the Indexing noise.enu. The wordbreaker in FTI is
neutral. I completely deleted the FT catalogs and manually recreated and
repopulated them. The results were the same.
Then I deleted the contents FTI's noise.enu except for a space and now, the
search simply does not return results. It seems like I can search for the
word can't on any other search engine but ours. Below is the query we are
passing using just the word *can't*. Can you tell if there is anything
within the query that might be better able to produce results?
select ARTICLE_ID, ARTICLE_STITLE, ARTICLE_LTITLE, ARTICLE_SUMMARY
,ARTICLE_STATUS, ARTICLE_APPROVED, DEPART_REF, USER_REF_OWNER,
USER_REF_EDITOR, ARTICLE_REVIEW_DATE, ARTICLE_EXPIRE, RANK,
PUBLIC_DIVISION_REF From ARTICLES as A, containstable(ARTICLES, *, '
"can''t" ') as AST where (LANGUAGE_REF= 1 and ARTICLE_DISABLE_SEARCH <> 1 )
and AST.[KEY] = A.ARTICLE_ID order by RANK desc
MIS query: "can''t"
TIA
Jake
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:%23BYyg8vrFHA.3096@.TK2MSFTNGP15.phx.gbl...
> "GitarJake" <spamaintme@.oz.net> wrote in message
> news:C0vRe.101151$Ep.81169@.lakeread02...
> Check the noise.X file in your FTDATA\Config folder (where X is the
> extension related to your word breaker language - eng for English, enu for
> US English, etc).
> 4 is in the list. I'm guessing that when 4.0 is passed FTS treats it a
> number and so finds it in the noise word file, whereas when 4.00.0 is used
> it's obviously not a number and so does a search for it as is.
> Can is a word in the noise word list too. I'm guessing FTS splits can't
> into can and not internally, and errors because can is in the list.
> What you could do is empty the noise word file - leave only a single line
> in it with a single space on it. Then run a full population. This has the
> effect of not erroring on any words (although some characters that are
> used as word breakers will still throw the noise words error, you'll have
> to filter these out in your query builder).
> Dan
>
|||To test this I added "Can't you see?" to the end of a book title in the pubs
database. I then created a new catalog on the title column, ran a full
population, and then ran this:
select * from titles where contains(title,'can''t')
and up popped the row I'd added the phrase to. I then tried this:
select a.* from titles a ,containstable(titles,*,'can''t') b where
a.title_id = b.[key]
select a.* from titles a ,containstable(titles,*,'"can''t"') b where
a.title_id = b.[key]
and again, I get the same row back from both versions. So it's not a problem
with the apostrophe, nor an issue with the double quotes.
The only other thing in your query that will prevent the row from appearing
will be if ARTICLE_DISABLE_SEARCH = 1 or LANGUAGE_REF <> 1. Have you tried
running the query with these clauses removed just in case?
Dan
GitarJake wrote on Sun, 4 Sep 2005 12:33:07 -0400:
[vbcol=seagreen]
> Hi Daniel,
> Thanks for responding.
> I have removed "can" from both the fulltext noise.enu word list in
> ftdata\config; and the Indexing noise.enu. The wordbreaker in FTI is
> neutral. I completely deleted the FT catalogs and manually recreated and
> repopulated them. The results were the same.
> Then I deleted the contents FTI's noise.enu except for a space and now,
> the search simply does not return results. It seems like I can search for
> the word can't on any other search engine but ours. Below is the query we
> are passing using just the word *can't*. Can you tell if there is
> anything within the query that might be better able to produce results?
> select ARTICLE_ID, ARTICLE_STITLE, ARTICLE_LTITLE, ARTICLE_SUMMARY
> ,ARTICLE_STATUS, ARTICLE_APPROVED, DEPART_REF, USER_REF_OWNER,
> USER_REF_EDITOR, ARTICLE_REVIEW_DATE, ARTICLE_EXPIRE, RANK,
> PUBLIC_DIVISION_REF From ARTICLES as A, containstable(ARTICLES, *, '
> "can''t" ') as AST where (LANGUAGE_REF= 1 and ARTICLE_DISABLE_SEARCH <>
> 1 ) and AST.[KEY] = A.ARTICLE_ID order by RANK desc
> MIS query: "can''t"
> TIA
> Jake
> "Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message news:%23BYyg8vrFHA.3096@.TK2MSFTNGP15.phx.gbl...