Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. PRB: Dashes '-' Ignored in Search with SQL Full-Text and MSIDXS Queries

PRB: Dashes '-' Ignored in Search with SQL Full-Text and MSIDXS Queries

View products that this article applies to.

Symptoms

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.

↑ Back to the top

Cause

 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.". 

↑ Back to the top

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.

↑ Back to the top

Status

This behavior is by design.

↑ Back to the top

More Information

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.

↑ Back to the top

References

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 DOCERR: Prefix Expansion with Index Server OLEDB Provider 2.0

↑ Back to the top

Keywords: kb, kbdatabase, kbdsupport, kbprb, kbbillprodsweep

↑ Back to the top

Article Info
Article ID : 200043
Revision : 1
Created on : 1/7/2017
Published on : 2/23/2011
Exists online : False
Views : 429