Searching for words with special characters in FTS 2008R2

Question

We have a customer who has many products.  Some of these products have the slash character (/) in them.  So, for example, they have these products:

• RIO/A
• RIO/M
• RIO/X

We have a table (call it MyTable) that contains a set of documents, including title, description, keywords and a VARBINARY(MAX) column that contains the PDF document itself.  We've created a FT index on that table that covers all columns.  These documents contain information pertaining to our customer's product line.

Our customer would like to be able to do a full-text search for their products, and have them return the appropriate values.  Because of word-breaker/noise word rules, this search returns everything with the word RIO in it, regardless of the /:

SELECT * FROM CONTAINSTABLE(MyTable, *, '"RIO/A"')

Others have suggested that we try replacing the slashes with the word SLASH and put in some post-processing, but obviously this isn't possible with the PDF in the table.

Is there any way that we can make this work correctly?  I have thought of something like removing "A", "M" and "X" from our noise-word list and then doing something like:

SELECT * FROM CONTAINSTABLE(MyTable, *, '"RIO A"')

Any other suggestions?

THanks, Jim

Answer 1

Hi Jim,

Based on your description, to search a phrase with special characters together, you may have a try to implement a custom dictionary to prevent the set of words or terms within it taking by a given Word Breaker. As a result, you can return the information with fully matched. For more information, please refer to: Creating Custom Dictionaries for special terms to be indexed 'as-is' in SQL Server 2008 Full-Text Indexes.

TechNet Subscriber Support
If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Stephanie Lv

TechNet Community Support

Answer 2

Thanks Stephanie, this sounds like it's nearly the perfect answer ... I say nearly, since the server's on a shared environment and getting OS-level stuff done, even restarting FDHosts, is a pain in the butt.  Still, I'll be able to make this work, no question.  Thanks again!