大网数据广告活动
数据库与应用安全

Dashes ignored in search with SQL Full-Text

· 52CE 我爱测

This article provides two workarounds for the problem that occurs when you perform a full-text search on SQL Server character data, or when you use a SQL distributed query with the Microsoft Index Server OLE DB provider (MSIDXS) and a prefix expansion search for a compound word that contains a hyphen (for example, XYZ-A*).

This article helps you work around the problem that occurs when you perform a full-text search on SQL Server character data, or when you use a SQL distributed query with the Microsoft Index Server OLE DB provider (MSIDXS) and a prefix expansion search for a compound word that contains a hyphen (for example, XYZ-A*).

Applies to:   SQL Server
Original KB number:   200043

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 (MSIDXS) 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.

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.

Workaround

To work around this problem, try one of the following methods:

  • Only use alphanumeric characters when using the SQL Server full-text index facilities.

  • Where non-alphanumeric character must be used in the search criteria (primarily the dash - character), use the Transact-SQL LIKE clause instead of the FULLTEXT or CONTAINS predicates.

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 MSIDXS provider to search for file system data. Using the dash (-) in a proximity search is not supported and may give unexpected results.

References

  • For more information on SQL Server full-text search, see the SQL Server Books Online.

  • For more information on the use of the CONTAINS clause with the Microsoft Index Server (MSIDXS) provider, see the Index Server documentation in the Windows NT 4.0 Option Pack documentation.

Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors. Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 01/15/2021
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。