Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 200043 - Last Review: February 23, 2011 - Revision: 5.0
PRB: Dashes '-' Ignored in Search with SQL Full-Text and MSIDXS Queries
This article was previously published under Q200043
When performing a full-text search on SQL Server character
data, or when using a SQL distributed query with the Microsoft Index Server OLE
DB provider (MSDIXS) and a prefix expansion search for a compound word that
contains a hyphen (for example, "XYZ-A*"), the results produced may not be as
expected.
 A full-text search considers a word to be a string of characters without spaces or punctuation. The occurrence of a non-alphanumeric character can "break" a word during a search. Because the SQL Server full-text search is a word-based engine, punctuation generally is not considered and is ignored when searching the index. Therefore, a CONTAINS clause like 'CONTAINS(testing, "computer-failure")' would match a row with the value, "The failure to find my computer would be expensive.".Â
WORKAROUND
To work around this problem, try the following:
- Only use alphanumeric characters when using the SQL Server
full-text index facilities.
-or- - Where non-alphanumeric character must be used in the search
critera (primarily the dash '-' character), use the Transact-SQL LIKE clause
instead of the FULLTEXT or CONTAINS predicates.
This behavior is by design.
Microsoft SQL Server version 7.0 provides the ability to
perform a full-text query on character data stored in SQL Server tables. You
can also use a SQL distributed query with the MSDIXS provider to search for
file system data. Using the dash ('-') in a proximity search is not supported
and may give unexpected results.
For more details on SQL Server full-text search, refer to
the SQL Server Books Online.
For more information on the use of the
CONTAINS clause with the Microsoft Index Server (MSDIXS) provider, refer to the
Index Server documentation in the Windows NT 4.0 Option Pack
documentation.
For additional information about
how full-text search and the LIKE clause are implemented in SQL Server 7.0,
click the following article number to view the article in the Microsoft
Knowledge Base:
182829Â
(http://kbalertz.com/Feedback.aspx?kbNumber=182829/EN-US/
)
DOCERR: Prefix Expansion with Index Server OLEDB Provider 2.0
APPLIES TO
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 2000 Enterprise Edition
- Microsoft SQL Server 7.0 Standard Edition
- Microsoft SQL Server 2005 Developer Edition
- Microsoft SQL Server 2005 Enterprise Edition
- Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems
- Microsoft SQL Server 2005 Enterprise X64 Edition
- Microsoft SQL Server 2005 Standard Edition
- Microsoft SQL Server 2005 Standard X64 Edition
- Microsoft SQL Server 2008 Developer
- Microsoft SQL Server 2008 Enterprise
- Microsoft SQL Server 2008 Standard
- Microsoft SQL Server 2008 R2 Developer
- Microsoft SQL Server 2008 R2 Enterprise
- Microsoft SQL Server 2008 R2 Standard
| kbdatabase kbprb KB200043 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate