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 Search

I have a strange issue with full text search. I have a table that has only 700 records in it, when I enable full text search and follow the wizard the index is not getting build. the storage full text say it is populating and it never does. I have restarted the service and rebuild the index few times with no benefits. I can do the same steps on another server with the same database and it works just fine. does anyone have any hints or things I can try? Please help

Check what are privileges associated with the login used to create this fulltext, also check whether MSsearch service is up and running.

Did you get any errors or warnings during thsi process?

|||

MSFTESQL is up and running. I don't get any errors when creating the Index. everything is successful status but when I am looking the property of the full text under storage the status is always "populating Catalog". The only error I can see is in the event viewer-

Performance Counters could not be loaded for msftesqlFD for instance MSSQLSERVER due to the following error: The system cannot find the file specified. 0x80070002.

not sure if this is related to full text search?

Please help.

Full text index deteriorating in use

Very strange situation on one of our SQL 2005 boxes.

- A Full text catalog containing a single full text index
- The index is of a single table containing a uniqueidentifier column and five varchar(max) columns, all indexed
- About 300,000 rows in the table

If I create the index and immediately run a query like this:

SELECT COUNT(*) FROM FREETEXTTABLE(tablename, columnname, 'term1 term2 term3', 5000)

I get the expected result: 5000 (there are actually about 185,000 matching rows if that same query is run without the top_n_by_rank parameter).

But then if I run the same query repeatedly over the next 10-15 minutes, the returned value decreases...4616, 3505...ending up around 507. It's like the full text index rots away before my eyes.

The database is purely read-only. We do run stored procedures against it that construct temporary tables, but nothing puts new data into this particular table. Doesn't matter whether the full-text index is set to manual or automatic repopulation. The timestamps on the index files on disk don't change. Nothing odd shows up in the Windows event logs or SQL Server logs.

I'm stumped. Any ideas?

http://www.sql-server-performance.com/full_text_search.asp

http://www.sql-server-performance.com/tb_search_optimization.asp