使用 SQL Server LIKE 模式搜索“全字匹配”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5444300/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Search for “whole word match” with SQL Server LIKE pattern
提问by mike nelson
Does anyone have a LIKEpattern that matches whole words only?
有没有人有LIKE只匹配整个单词的模式？
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 LIKEshould be able to do the trick. However if anyone has tested performance of Full Text Search against LIKEpatterns, I would be interested to hear.
我没有使用 SQL 全文搜索，因为它不可用。我认为在LIKE应该能够做到这一点时，没有必要进行简单的关键字搜索。但是，如果有人针对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”而不是“spit”。
E.g. DealTitlemight contain "a pit of despair" or "pit your wits" or "a pit" or "a pit." or "pit!" or just "pit".
例如，DealTitle可能包含“a pit of despair”或“pit your wits”或“a pit”或“a pit”。或“坑！” 或者只是“坑”。
回答by RichardTheKiwi
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
回答by Carlos Mendez SuperPlazascom
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!
我希望它可以帮助你们！
回答by Saleh Mosleh
you can just use below condition for whitespace delimiters:
您可以使用以下条件作为空格分隔符：
(' '+YOUR_FIELD_NAME+' ') like '% doc %'
it works faster and better than other solutions. so in your case it works fine with "a pit of despair" or "pit your wits" or "a pit" or "a pit." or just "pit", but not works for "pit!".
它比其他解决方案更快更好地工作。所以在你的情况下，它适用于“绝望的坑”或“坑你的智慧”或“坑”或“坑”。或者只是“坑”，但不适用于“坑！”。
回答by Helen Craigman
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]'
回答by zzzaaabbb
If you can use regexpoperator in your SQL query..
如果您可以regexp在 SQL 查询中使用运算符..
For finding any combination of spaces, punctuation and start/end of string as word boundaries:
查找空格、标点符号和字符串开头/结尾的任意组合作为单词边界：
where DealTitle regexp '(^|[[:punct:]]|[[:space:]])pit([[:space:]]|[[:punct:]]|$)'
回答by Andrey Hartung
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.WORDin the ND.TEXT_STRINGand you can use the WHEREclausule to filter this using some word.
有了这个，您可以列出ST.WORDin 中的所有发生率，ND.TEXT_STRING并且您可以使用WHEREclausule 使用某个词来过滤它。
回答by Eric
Surround your string with spaces and create a test column like this:
用空格包围你的字符串并创建一个像这样的测试列：
SELECT t.DealTitle
FROM yourtable t
CROSS APPLY (SELECT testDeal = ' ' + ISNULL(t.DealTitle,'') + ' ') fx1
WHERE fx1.testDeal LIKE '%[^a-z]pit[^a-z]%'
回答by Sankaranarayanan Rajkumar
Try using charindexto find the match:
尝试使用charindex查找匹配项：
Select *
from table
where charindex( 'Whole word to be searched', columnname) > 0
回答by Andomar
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:
然后您可以过滤返回的行客户端站点，添加它必须是整个单词的额外条件。例如，如果它匹配正则表达式：
\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 artcilehas the details of how to set up a dbo.RegexMatchfunction.
另一种选择是使用 SQL Server 2005 及更高版本中提供的 CLR 函数。这将允许您搜索正则表达式服务器端。这篇MSDN 文章详细介绍了如何设置dbo.RegexMatch功能。
