SQL 检查文本是否包含单词
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29386640/
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
SQL Check if a text contains a word
提问by fubo
I have a Text,
我有一个文本，
'Me and you against the world' // false
'Can i have an email address' // true
'This is an' // true
'an' //true
I want to check whether the word anis inside my String.
我想检查这个词an是否在我的字符串中。
How do I check if a text contains a specific word in SQL? I can't add a full-text catalog. Otherwies i could
如何检查文本是否包含 SQL 中的特定单词？我无法添加全文目录。否则我可以
SELECT * FROM TABLE WHERE CONTAINS(Text, 'an')
回答by gvee
Here's one approach.
这是一种方法。
DECLARE @table_name table (
column_name varchar(50)
);
INSERT INTO @table_name (column_name)
VALUES ('Me and you against the world')
, ('Can i have an email address')
, ('This is an')
;
SELECT column_name
FROM @table_name
WHERE ' ' + column_name + ' ' LIKE '% an %'
;
回答by Luka Milani
There are some way to do this, seem you want find a wordand not a part of a word, so you can do in easy way with like operator
有一些方法可以做到这一点，似乎你想找到一个词而不是一个词的一部分，所以你可以用简单的方法来做like operator
You can have 3 cases to found a word
你可以有 3 种情况来找到一个词
- 'space'WORD
- WORD'space'
- 'space'WORD'space'
- '空间'字
- 词'空间'
- '空格'WORD'空格'
SELECT * FROM TABLE WHERE Field like ' an' OR Field like 'an ' OR Field like ' an '
SELECT * FROM TABLE WHERE 像“an”这样的字段 OR像“an”这样的字段 OR像“an”这样的字段
Hope it helps
希望能帮助到你
回答by Javier Salazar
It is perfectly done in MS SQL Server by the CHARINDEXfunction (it is internal to MS SQL):
它通过CHARINDEX函数在 MS SQL Server 中完美完成（它是 MS SQL 的内部函数）：
if CHARINDEX('an ',@mainString) > 0
begin
--do something
end
The solution was showed before in another post.
之前在另一篇文章中给出了解决方案。
回答by pimbrouwers
The three cases you'll encounter as Luka mentions:
Luka 提到的三种情况：
- Space before word
- Space after word
- Space before and after word
- 单词前的空格
- 字后空格
- 单词前后的空格
To accomplish this, you'll write a query like the following which searches for the whole word, and pads the expression to search with a leading and trailing space to capture words at the start/end of the expression:
为此，您将编写一个如下所示的查询来搜索整个单词，并用前导和尾随空格填充要搜索的表达式，以捕获表达式开头/结尾的单词：
Note: I've used a contrived example to make this portable and demonstrable.
注意：我使用了一个人为的例子来使这个便携和演示。
select
t.txt
from (
select
'this is an awesome test of awesomeness man' as txt
) t
where
charindex(' an ', ' ' + t.txt + ' ') > 0;
