在SQL Server索引中搜索单词 [英] Search for words in SQL Server index

问题描述

我需要在全文搜索和索引搜索之间进行一些操作：

我想在表格的一列中搜索文本（可能在列上也会有一个索引，如果）

问题是，我想在列中搜索单词，但我不想匹配部分。

例如，我的专栏可能包含商业名称：

M emy Muck Miller and Partners Inc.

Boy &安培;黄油早餐公司

现在，如果我搜索 Miller ，我想找到第一行。但是，如果我搜索 iller ，我不想找到它，因为没有以iller开头的单词。不过，搜索 Break 应该找到 Boy& Butter Breakfast company ，因为有一个词以 Break 开头。 / p>

所以如果我尝试使用

  WHERE BusinessName LIKE％Break％ 
  

它会找到太多的点击。

是否有任何方法搜索由空格或其他分隔符分隔的单词？

（LINQ应该是最好的，普通的SQL也可以）

重要提示：空格不是唯一的分隔符！斜杠，冒号，圆点，所有非字母数字字符都应该考虑这个工作！

解决方案

SQL Server 2000或更高版本。

  SELECT * 
从dbo.TblBusinessNames 
 WHERE BusinessName like'％[^ Az ^ 0-9 ] Break％' - 在句子中间
或BusinessName like'Break％' - 在句子的开头
  

LIKE的关键字参考： http://msdn.microsoft.com/zh-cn/library/aa933232（SQL.80）.aspx

I need something in between a full text search and an index search:
I want to search for text in one column of my table (probably there will be an index on the column, too, if that matters).

Problem is, I want to search for words in the column, but I don't want to match parts.

For example, my column might contain business names:
Mighty Muck Miller and Partners Inc.
Boy & Butter Breakfast company

Now if I search for "Miller" I want to find the first line. But if I search for "iller" I don't want to find it, because there is no word starting with "iller". Searching for "Break" should find "Boy & Butter Breakfast company", though, since one word is starting with "Break".

So if I try and use

WHERE BusinessName LIKE %Break%

it will find too many hits.

Is there any way to Search for Words separated by whitespace or other delimiters?

(LINQ would be best, plain SQL would do, too)

Important: Spaces are by far not the only delimiters! Slashes, colons, dots, all non-alphanumerical characters should be considered for this to work!

解决方案

SQL Server 2000 or above.

SELECT *
  FROM dbo.TblBusinessNames
 WHERE BusinessName like '%[^A-z^0-9]Break%' -- In the middle of a sentence
    OR BusinessName like 'Break%'            -- At the beginning of a sentence

Keyword Reference for LIKE: http://msdn.microsoft.com/en-us/library/aa933232(SQL.80).aspx

这篇关于在SQL Server索引中搜索单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！