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 and sepcial characters like &(ampersand)

I think this may have been asked for million times... but for some strange reason, i couldn't find anything related about that in this forum.....

So, just wondering if there is any way to handle special characters like "&" in full text search ?

like "AT&T" ?

cause when I do a simple select * contains AT&T, it won't return any result ...

thank you in advance.

I believe this is because of the noise word filter. Sql treats & as a word breaker, so what Sql is actually executing is "AT T"

However, if you look at the noise file, both "at" and "t" are filtered out of full text searches. So my suggestion would be to take these two out of the appropriate noise file and check the search again. Otherwise, you're probably going to have to do some transformation of data.

You can test this theory by inserting a new record like Jones&Smith into the same column that AT&T is in. If you can get it to return results against either "Jones" or "Smith" than the noise filter is your problem.|||

thank you so much
I think that is the problem

but then I have another problem...
since my website is host on a share server... i don't have access to the noise file...

Is there anything I can do on my side so that i can include the noise words that are defined in the nosie file?

thank you so much

|||That is a good question. My inclination is that you are SOL. An alternative would have to replace all the ampersands in that column with a token or something. An example would be AT[AMP]T

But then you would be responsible for everywhere in your code, to know to replace an & going in with [AMP], and also take it back to & when it comes back out.|||

Thank you so much

But i guess that would mean a whole lot of replacing to handle...

haha, so there isn't any way... to tell the sql server to .... "hey, ignore the noise words when doing full text index for a specific table", right?

haha

thank you so much

-------------------

大作

|||The only thing I have been able to find is this thread which suggests using quotes, but the answer appears to be in FREETEXT. Take a look at the FREETEXT option and see if it does what you need.

http://groups.google.com/group/microsoft.public.sqlserver.fulltext/browse_thread/thread/235f8f5cb0f12c47/edad245b4216abe6%23edad245b4216abe6sql

Full text indexing with Japanese characters problem

Hi all,
I am quite experimented with SQL Server, but not that much with full
text indexing. After some successful attempts with english fields, I've
decided to try it with Japanese characters. I don't know why, but it
seems to have a strange behaviour.
As in this screenshot
(http://img65.imageshack.us/img65/980/jap3xt.gif), the CONTAINS
function does not seem to return only fields with an exact word match
of the given "word" (query), but also strange results which does not
even correspond to the query. Can anybody help me with that one?
Thanks!
ibiza
I'm not sure about your problem, but could I have a copy of your DB?
That looks amazingly interesting I love japanese ^_^
|||well, if you can help me with that problem, I'd be glad to consider it

(be warned though, it's more than 500MB in size...)
But seriously, I really don't understand why is the full-text indexing
service getting me those "almost random" rows where the dic_kana column
does not contain an exact match...yes, both the query and most of the
returned rows stard with "=E3=81=8A=E3=82=82", but that's the only resembla=
nce I
can find...
please help!
thanks
ibiza
|||well, if you can help me with that problem, I'd be glad to consider it

(be warned though, it's more than 500MB in size...)
But seriously, I really don't understand why is the full-text indexing
service getting me those "almost random" rows where the dic_kana column
does not contain an exact match...yes, both the query and most of the
returned rows stard with "=E3=81=8A=E3=82=82", but that's the only resembla=
nce I
can find...
someone please help!
thanks
ibiza
|||Does text indexing support non-latin characters?
|||Hi again,
I did some other tests with english this time, and they all seemed
successful...until this one :
http://img155.imageshack.us/img155/2001/untitled1tx.gif
Why is the CONTAINS returning no row at all? ;_;
This makes me doubt about the validity of all the previous succesful
queries I made...Could there be missing words, if some queries like the
one with 'now' isn't returning any at all?
What's the problem with full text indexing? :S
And yes I did repopulate all the indexes before running this query...
help would be greatly appreciated!
thanks
ibiza

Full text indexing with Japanese characters problem

Hi all,
I am quite experimented with SQL Server, but not that much with full
text indexing. After some successful attempts with english fields, I've
decided to try it with Japanese characters. I don't know why, but it
seems to have a strange behaviour.
As in this screenshot
(http://img65.imageshack.us/img65/980/jap3xt.gif), the CONTAINS
function does not seem to return only fields with an exact word match
of the given "word" (query), but also strange results which does not
even correspond to the query. Can anybody help me with that one?
Thanks!
ibiza
This is probably due to the way the Japanese characters are broken by the
word breakers at index time and stored in the full text index. For example
Japanese consists of 5 different character sets. Japanese "words" are
largely syllables, so when you search on a "word" what it matches to are a
variety of sub tokens/syllables.
What you need to do is have someone who is fluent in Japanese verify that
your search application is actually finding what you are looking for. My
knowledge of Japanese is limited to a few phrases.
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
"ibiza" <lambertb@.gmail.com> wrote in message
news:1143825609.907248.76110@.t31g2000cwb.googlegro ups.com...
> Hi all,
> I am quite experimented with SQL Server, but not that much with full
> text indexing. After some successful attempts with english fields, I've
> decided to try it with Japanese characters. I don't know why, but it
> seems to have a strange behaviour.
>
> As in this screenshot
> (http://img65.imageshack.us/img65/980/jap3xt.gif), the CONTAINS
> function does not seem to return only fields with an exact word match
> of the given "word" (query), but also strange results which does not
> even correspond to the query. Can anybody help me with that one?
>
> Thanks!
>
> ibiza
>
|||Hi,
thanks a lot for your reply
Well, even if I am not "fluent" in Japanese, I can still read japanese
"hiragana" characters, that is, what my query is about.
Each symbol can be translated as a syllable, so =E3=81=8A=E3=82=82=E3=81=97=
=E3=82=8D (the
query) would be "omoshiro", "o-mo-shi-ro".
The three first rows are translated as "omoni", "omo" and "omoi". So
I'm wondering why these results are showing, because they only partly
contains the query word (the =E3=81=8A=E3=82=82 ("omo") part)
As far as I know Japanese and full text indexing, the query should only
return results where the full and comple quey is found (=E3=81=8A=E3=82=82=
=E3=81=97=E3=82=8D),
as I did a CONTAINS search with a whole word.
Can you help me a little more with that information?
thanks again
ibiza
|||This could be by design. For example in German a word like wanderlust is
broken as wardern, lust and wanderlist. So if you were to do a contains
query for wanderlust you would get hits to any of these words. It looks like
it is doing something similar.
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
"ibiza" <lambertb@.gmail.com> wrote in message
news:1143922571.663177.238920@.i39g2000cwa.googlegr oups.com...
Hi,
thanks a lot for your reply
Well, even if I am not "fluent" in Japanese, I can still read japanese
"hiragana" characters, that is, what my query is about.
Each symbol can be translated as a syllable, so ? (the
query) would be "omoshiro", "o-mo-shi-ro".
The three first rows are translated as "omoni", "omo" and "omoi". So
I'm wondering why these results are showing, because they only partly
contains the query word (the ? ("omo") part)
As far as I know Japanese and full text indexing, the query should only
return results where the full and comple quey is found (?),
as I did a CONTAINS search with a whole word.
Can you help me a little more with that information?
thanks again
ibiza
|||Hi there,
thanks again for your reply.
I did some other tests with english and they all seemed successful
until this one :
http://img155.imageshack.us/img155/2001/untitled1tx.gif
Why is the CONTAINS returning no row at all? ;_;
This makes me doubt about the validity of all the previous succesful
queries I made...Could there be missing words, if some queries like the
one with 'now' isn't returning any at all?
What's the problem with full text indexing? :S
And yes I did repopulate all the indexes before running this query...
thanks for your help!
ibiza

Full text indexing with Japanese characters problem

Hi all,
I am quite experimented with SQL Server, but not that much with full
text indexing. After some successful attempts with english fields, I've
decided to try it with Japanese characters. I don't know why, but it
seems to have a strange behaviour.
As in this screenshot
(http://img65.imageshack.us/img65/980/jap3xt.gif), the CONTAINS
function does not seem to return only fields with an exact word match
of the given "word" (query), but also strange results which does not
even correspond to the query. Can anybody help me with that one?
Thanks!
ibizaI'm not sure about your problem, but could I have a copy of your DB?
That looks amazingly interesting I love japanese ^_^|||well, if you can help me with that problem, I'd be glad to consider it

(be warned though, it's more than 500MB in size...)
But seriously, I really don't understand why is the full-text indexing
service getting me those "almost random" rows where the dic_kana column
does not contain an exact match...yes, both the query and most of the
returned rows stard with "=E3=81=8A=E3=82=82", but that's the only resembla=
nce I
can find...
please help!
thanks
ibiza|||well, if you can help me with that problem, I'd be glad to consider it

(be warned though, it's more than 500MB in size...)
But seriously, I really don't understand why is the full-text indexing
service getting me those "almost random" rows where the dic_kana column
does not contain an exact match...yes, both the query and most of the
returned rows stard with "=E3=81=8A=E3=82=82", but that's the only resembla=
nce I
can find...
someone please help!
thanks
ibiza|||Does text indexing support non-latin characters?|||Hi again,
I did some other tests with english this time, and they all seemed
successful...until this one :
http://img155.imageshack.us/img155/2001/untitled1tx.gif
Why is the CONTAINS returning no row at all' ;_;
This makes me doubt about the validity of all the previous succesful
queries I made...Could there be missing words, if some queries like the
one with 'now' isn't returning any at all?
What's the problem with full text indexing? :S
And yes I did repopulate all the indexes before running this query...
help would be greatly appreciated!
thanks
ibiza

Full text indexing with Japanese characters problem

Hi all,
I am quite experimented with SQL Server, but not that much with full
text indexing. After some successful attempts with english fields, I've
decided to try it with Japanese characters. I don't know why, but it
seems to have a strange behaviour.
As in this screenshot
(http://img65.imageshack.us/img65/980/jap3xt.gif), the CONTAINS
function does not seem to return only fields with an exact word match
of the given "word" (query), but also strange results which does not
even correspond to the query. Can anybody help me with that one?
Thanks! :)
ibizaI'm not sure about your problem, but could I have a copy of your DB?
That looks amazingly interesting :) I love japanese ^_^|||well, if you can help me with that problem, I'd be glad to consider it
:)
(be warned though, it's more than 500MB in size...)
But seriously, I really don't understand why is the full-text indexing
service getting me those "almost random" rows where the dic_kana column
does not contain an exact match...yes, both the query and most of the
returned rows stard with "=E3=81=8A=E3=82=82", but that's the only resembla=nce I
can find...
please help!
thanks :)
ibiza|||well, if you can help me with that problem, I'd be glad to consider it
:)
(be warned though, it's more than 500MB in size...)
But seriously, I really don't understand why is the full-text indexing
service getting me those "almost random" rows where the dic_kana column
does not contain an exact match...yes, both the query and most of the
returned rows stard with "=E3=81=8A=E3=82=82", but that's the only resembla=nce I
can find...
someone please help!
thanks :)
ibiza|||Does text indexing support non-latin characters?|||Hi again,
I did some other tests with english this time, and they all seemed
successful...until this one :
http://img155.imageshack.us/img155/2001/untitled1tx.gif
Why is the CONTAINS returning no row at all' ;_;
This makes me doubt about the validity of all the previous succesful
queries I made...Could there be missing words, if some queries like the
one with 'now' isn't returning any at all?
What's the problem with full text indexing? :S
And yes I did repopulate all the indexes before running this query...
help would be greatly appreciated!
thanks
ibiza

Full text indexing with Japanese characters problem

Hi all,
I am quite experimented with SQL Server, but not that much with full
text indexing. After some successful attempts with english fields, I've
decided to try it with Japanese characters. I don't know why, but it
seems to have a strange behaviour.
As in this screenshot
(http://img65.imageshack.us/img65/980/jap3xt.gif), the CONTAINS
function does not seem to return only fields with an exact word match
of the given "word" (query), but also strange results which does not
even correspond to the query. Can anybody help me with that one?
Thanks! :)
ibizaHi again,
I did some other tests with english this time, and they all seemed
successful...until this one :
http://img155.imageshack.us/img155/2001/untitled1tx.gif
Why is the CONTAINS returning no row at all' ;_;
This makes me doubt about the validity of all the previous succesful
queries I made...Could there be missing words, if some queries like the
one with 'now' isn't returning any at all?
What's the problem with full text indexing? :S
And yes I did repopulate all the indexes before running this query...
help would be greatly appreciated!
thanks
ibiza

Full text indexing with Japanese characters problem

Hi all,

I am quite experimented with SQL Server, but not that much with full
text indexing. After some successful attempts with english fields, I've
decided to try it with Japanese characters. I don't know why, but it
seems to have a strange behaviour.

As in this screenshot
(http://img65.imageshack.us/img65/980/jap3xt.gif), the CONTAINS
function does not seem to return only fields with an exact word match
of the given "word" (query), but also strange results which does not
even correspond to the query. Can anybody help me with that one?

Thanks! :)

ibizaI'm not sure about your problem, but could I have a copy of your DB?
That looks amazingly interesting :) I love japanese ^_^|||well, if you can help me with that problem, I'd be glad to consider it
:)
(be warned though, it's more than 500MB in size...)

But seriously, I really don't understand why is the full-text indexing
service getting me those "almost random" rows where the dic_kana column
does not contain an exact match...yes, both the query and most of the
returned rows stard with "おも", but that's the only resemblance I
can find...

please help!

thanks :)
ibiza|||well, if you can help me with that problem, I'd be glad to consider it
:)
(be warned though, it's more than 500MB in size...)

But seriously, I really don't understand why is the full-text indexing
service getting me those "almost random" rows where the dic_kana column
does not contain an exact match...yes, both the query and most of the
returned rows stard with "おも", but that's the only resemblance I
can find...

someone please help!

thanks :)
ibiza|||Does text indexing support non-latin characters?|||Hi again,

I did some other tests with english this time, and they all seemed
successful...until this one :
http://img155.imageshack.us/img155/2001/untitled1tx.gif

Why is the CONTAINS returning no row at all?? ;_;
This makes me doubt about the validity of all the previous succesful
queries I made...Could there be missing words, if some queries like the
one with 'now' isn't returning any at all?

What's the problem with full text indexing? :S

And yes I did repopulate all the indexes before running this query...

help would be greatly appreciated!
thanks

ibiza

Full text indexing parentheses and special characters

select @.@.version
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
How you tell the indexer (SQL 2K with WIN2K) to include () , /, - and all other special characters while generating the index? These characters aren't in the noise.eng file and I can't seem to figure out how to index them.
The reason we need these characters indexed is because of searches like "4 (1 1/2)" where only records with the exact phrase should be returned. Currently records with 4 1/2 and 4 1 1/2 are being returned as well and we need to eliminate them.
Naresh
Naresh,
First of all, thanks for providing your SQL Server and OS platform version
info as the latter is especially important for this issue.
Unfortunately, the means to control the use of punctuation (all the special
characters) is not accessible, nor controllable via any registry key/value,
dbcc or trace flag as this is a big issue for many SQL FTS customers that is
not addressed in SQL Server 2000 with the MSSearch service.
There is some improvement via the OS-supplied wordbreaker in Win2003
(langwrbk.dll) over the Win2K supplied wordbreaker (infosoft.dll), however,
neither OS provides any means to control how the MSSearch service handles
these use of punctuation in the FT indexing process. Note, this may or may
not change in the next release of SQL Server 2005 (codename Yukon) that will
ship with its own wordbreaker (langwrbk.dll).
You may also want to experiment with using FREETEXT vs. CONTAINS on Win2K
and Win2003 to get the best results possible in your environment.
Regards,
John
PS: If I had my way this and the noise word file would be at the table level
and not at the OS level, but then that's just wishful thinking after many
years of having to answer this question... :-)
"Naresh Rajpal" <RajpalN@.sullcrom.com> wrote in message
news:97E163D6-1CB6-4947-B9AA-096FC28FC631@.microsoft.com...
> select @.@.version
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> How you tell the indexer (SQL 2K with WIN2K) to include () , /, - and all
other special characters while generating the index? These characters
aren't in the noise.eng file and I can't seem to figure out how to index
them.
> The reason we need these characters indexed is because of searches like "4
(1 1/2)" where only records with the exact phrase should be returned.
Currently records with 4 1/2 and 4 1 1/2 are being returned as well and we
need to eliminate them.
> Naresh
>
>

Full text indexing parentheses and other special characters

How you tell the indexer (SQL 2K with WIN2K) to include () , /, - and all other special characters while generating the index? These characters aren't in the noise.eng file and I can't seem to figure out how to index them.
The reason we need these characters indexed is because of searches like "4 (1 1/2)" where only records with the exact phrase should be returned. Currently records with 4 1/2 and 4 1 1/2 are being returned as well and we need to eliminate them.
Naresh
Naresh,
Please try the .fulltext newsgroup.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Naresh Rajpal" <rajpaln@.sullcrom.com> wrote in message
news:8F54AB5A-99BA-4E80-9DD4-16C48F21AF8B@.microsoft.com...
> How you tell the indexer (SQL 2K with WIN2K) to include () , /, - and all
other special characters while generating the index? These characters
aren't in the noise.eng file and I can't seem to figure out how to index
them.
> The reason we need these characters indexed is because of searches like "4
(1 1/2)" where only records with the exact phrase should be returned.
Currently records with 4 1/2 and 4 1 1/2 are being returned as well and we
need to eliminate them.
> Naresh

Full text indexing parentheses and other special characters

How you tell the indexer (SQL 2K with WIN2K) to include () , /, - and all ot
her special characters while generating the index? These characters aren't
in the noise.eng file and I can't seem to figure out how to index them.
The reason we need these characters indexed is because of searches like "4 (
1 1/2)" where only records with the exact phrase should be returned. Current
ly records with 4 1/2 and 4 1 1/2 are being returned as well and we need to
eliminate them.
NareshNaresh,
Please try the .fulltext newsgroup.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Naresh Rajpal" <rajpaln@.sullcrom.com> wrote in message
news:8F54AB5A-99BA-4E80-9DD4-16C48F21AF8B@.microsoft.com...
> How you tell the indexer (SQL 2K with WIN2K) to include () , /, - and all
other special characters while generating the index? These characters
aren't in the noise.eng file and I can't seem to figure out how to index
them.
> The reason we need these characters indexed is because of searches like "4
(1 1/2)" where only records with the exact phrase should be returned.
Currently records with 4 1/2 and 4 1 1/2 are being returned as well and we
need to eliminate them.
> Naresh

Full text indexing parentheses and other special characters

How you tell the indexer (SQL 2K with WIN2K) to include () , /, - and all other special characters while generating the index? These characters aren't in the noise.eng file and I can't seem to figure out how to index them.
The reason we need these characters indexed is because of searches like "4 (1 1/2)" where only records with the exact phrase should be returned. Currently records with 4 1/2 and 4 1 1/2 are being returned as well and we need to eliminate them.
NareshNaresh,
Please try the .fulltext newsgroup.
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Naresh Rajpal" <rajpaln@.sullcrom.com> wrote in message
news:8F54AB5A-99BA-4E80-9DD4-16C48F21AF8B@.microsoft.com...
> How you tell the indexer (SQL 2K with WIN2K) to include () , /, - and all
other special characters while generating the index? These characters
aren't in the noise.eng file and I can't seem to figure out how to index
them.
> The reason we need these characters indexed is because of searches like "4
(1 1/2)" where only records with the exact phrase should be returned.
Currently records with 4 1/2 and 4 1 1/2 are being returned as well and we
need to eliminate them.
> Naresh