使用类似于模式的SQL Server搜索“全词匹配”

Does anyone have a LIKE pattern that matches whole words only?

有人有类似的模式，只匹配整个词吗?

It needs to account for spaces, punctuation, and start/end of string as word boundaries.

它需要将空格、标点符号和字符串的开始/结束作为单词边界。

I am not using SQL Full Text Search as that is not available. I don't think it would be necessary for a simple keyword search when LIKE should be able to do the trick. However if anyone has tested performance of Full Text Search against LIKE patterns, I would be interested to hear.

我没有使用SQL全文搜索，因为这是不可用的。我认为对于一个简单的关键字搜索来说，在LIKE能够做到这一点的时候是没有必要的。但是，如果有人测试过全文搜索的性能，我很想听听。

Edit:

I got it to this stage, but it does not match start/end of string as a word boundary.

我把它放到了这个阶段，但是它并不把字符串的开始/结束作为一个单词的边界。

where DealTitle like '%[^a-zA-Z]pit[^a-zA-Z]%'

I want this to match "pit" but not "spit" in a sentence or as a single word.

我想让它与“pit”匹配，而不是把“唾液”作为一个句子或一个单词。

E.g. DealTitle might contain "a pit of despair" or "pit your wits" or "a pit" or "a pit." or "pit!" or just "pit".

DealTitle可以是“绝望的深渊”、“绞尽脑汁”、“陷阱”、“陷阱”或“陷阱”，也可以是“陷阱”。

5 个解决方案

#1

Full text indexes is the answer.

答案是全文索引。

The poor cousin alternative is

可怜的表兄弟选择是

'.' + column + '.' LIKE '%[^a-z]pit[^a-z]%'

FYI unless you are using _CS collation, there is no need for a-zA-Z

除非您使用的是_CS排序，否则不需要a-zA-Z。

#2

I think the recommended patterns exclude words with do not have any character at the beginning or at the end. I would use the following additional criteria.

我认为推荐的模式排除了开头和结尾没有任何字符的词。我将使用以下附加条件。

where DealTitle like '%[^a-z]pit[^a-z]%' OR 
  DealTitle like 'pit[^a-z]%' OR 
  DealTitle like '%[^a-z]pit'

I hope it helps you guys!

我希望它能帮到你们!

#3

Another simple alternative:

另一个简单的选择:

WHERE DealTitle like '%[^a-z]pit[^a-z]%' OR 
      DealTitle like '[^a-z]pit[^a-z]%' OR 
      DealTitle like '%[^a-z]pit[^a-z]'

#4

This is a good topic and I want to complement this to someone how needs to find some word in some string passing this as element of a query.

这是一个很好的主题，我想补充一下如何在字符串中找到某个单词，将其作为查询的元素传递。

SELECT 
    ST.WORD, ND.TEXT_STRING
FROM 
    [ST_TABLE] ST
LEFT JOIN 
    [ND_TABLE] ND ON ND.TEXT_STRING LIKE '%[^a-z]' + ST.WORD + '[^a-z]%'
WHERE 
    ST.WORD = 'STACK_OVERFLOW' -- OPTIONAL

With this you can list all the incidences of the ST.WORD in the ND.TEXT_STRING and you can use the WHERE clausule to filter this using some word.

有了这个，你就可以列出ND中所有的ST.WORD事件。TEXT_STRING，您可以使用WHERE clausule来过滤这个使用某个单词。

#5

-1

You could search for the entire string in SQL:

您可以使用SQL搜索整个字符串:

select * from YourTable where col1 like '%TheWord%'

Then you could filter the returned rows client site, adding the extra condition that it must be a whole word. For example, if it matches the regex:

然后，您可以过滤返回的行客户端站点，添加一个额外条件，即它必须是一个完整的单词。例如，如果它匹配regex:

\bTheWord\b

Another option is to use a CLR function, available in SQL Server 2005 and higher. That would allow you to search for the regex server-side. This MSDN artcile has the details of how to set up a dbo.RegexMatch function.

另一种选择是使用CLR函数，可以在SQL Server 2005或更高版本中使用。这将允许您搜索regex服务器端。这个MSDN artcile有如何设置dbo的详细信息。RegexMatch函数。可以

#1