SQL 只匹配整个单词与 LIKE？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6283767/
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
Match only entire words with LIKE?
提问by Abhinav Sharma
So 'awesome document' LIKE '%doc%' is true, because doc is a sub-string. But, I want it to be false while 'awesome doc' or 'doc awesome' or 'awesome doc awesome' should be true. How can I do with with a like?
所以 'awesome document' LIKE '%doc%' 是真的，因为 doc 是一个子字符串。但是，我希望它是假的，而 'awesome doc' 或 'doc awesome' 或 'awesome doc awesome' 应该是真的。点个赞怎么办？
I'm using sqlite, so I hope I don't have to use something that isn't available.
我正在使用 sqlite，所以我希望我不必使用不可用的东西。
回答by YetAnotherUser
How about split it into four parts -
把它分成四部分怎么样——
[MyColumn] Like '% doc %'
OR [MyColumn] Like '% doc'
OR [MyColumn] Like 'doc %'
OR [MyColumn] = 'doc'
Edit: An alternate approach (only for ascii chars) could be:
编辑：另一种方法（仅适用于 ascii 字符）可能是：
'#'+[MyColumn]+'#' like '%[^a-z0-9]doc[^a-z0-9]%'
(You may want to take care of any special char as well)
（您可能还想处理任何特殊字符）
It doesn't look like, but you may want to explore Full Text Searchand Contains, in case that's more suitable for your situation.
它看起来不像，但您可能想要探索Full Text Search和Contains，以防它更适合您的情况。
See: - MSDN: [ ] (Wildcard - Character(s) to Match) (Transact-SQL)
回答by FishBasketGordo
WHERE (@string LIKE 'doc %' OR @string LIKE '% doc' OR @string LIKE '% doc %' OR @string = 'doc')
回答by Saleh Mosleh
you can just use below condition:
您可以使用以下条件：
(' '+YOUR_FIELD_NAME+' ') like '% doc %'
it works faster than other solutions.
它比其他解决方案工作得更快。
回答by awestover89
You could use some ORs, LIKE '% doc %' OR LIKE 'doc %' OR LIKE '% doc' OR LIKE 'doc'
您可以使用一些 OR，例如 '% doc %' OR LIKE 'doc %' OR LIKE '% doc' OR LIKE 'doc'
回答by Fandango68
None of the answers worked for me (SQL Server 2008 R2).
没有一个答案对我有用（SQL Server 2008 R2）。
But I managed to get it to work using somewhat of the answers shown below..
但我设法使用下面显示的一些答案让它工作。
SELECT * FROM myTable AS v
WHERE v.Note LIKE '%[^a-zA-Z0-9]CONTainers[^a-zA-Z0-9]%'
Note:v.Note is a TEXT data type
注意：v.Note 是 TEXT 数据类型
This worked for me with the following samples..
这对我有用以下示例..
- containers
- CONTAINERS
- conTaInERS
- 集装箱
- 容器
- 集装箱
But not when the word 'containers' was surrounded by other letters or numerics..
但是当“容器”这个词被其他字母或数字包围时就不会了。
- 001containers001
- AAcontainersAA
- 001容器001
- AAcontainersAA
Which is what the OP asked for in the first place.
这就是 OP 首先要求的。
回答by Panos K.
LIKE is quite limited. I would suggest REGEXP, for example:
LIKE 非常有限。我会建议 REGEXP，例如：
columnName REGEXP 'doc[;.?!,") ]'
for example: awesome document wouldn't match, but awesome doc, docawesome, awesome docawesome will match.
例如： awesome 文档不匹配，但 awesome doc、 docawesome、 awesome docawesome 将匹配。
It will also match an existing word in text followed by punctuation characters. You can try any example here: https://regexr.com/
它还将匹配文本中的现有单词，后跟标点符号。您可以在这里尝试任何示例：https: //regexr.com/
回答by patapizza
What about NOT LIKE '%doc%'?
怎么样NOT LIKE '%doc%'？
