SQL sql在一个字符串中搜索多个单词
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6506418/
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 searching multiple words in a string
提问by user181218
What is the most efficient and elegant SQL query looking for a string containing the words "David", "Moses" and "Robi". Assume the table is named T and the column C.
查找包含单词 "David"、"Moses" 和 "Robi" 的字符串的最有效和最优雅的SQL 查询是什么。假设表名为 T，列名为 C。
回答by Pranay Rana
Select * from table where
columnname like'%David%' and
columnname like '%Moses%' and columnname like'%Robi%'
回答by Neil Knight
In SQL Server 2005+ with Full-Text indexing switched on, I'd do the following:
在启用全文索引的 SQL Server 2005+ 中，我会执行以下操作：
SELECT *
FROM T
WHERE CONTAINS(C, '"David" OR "Robi" OR "Moses"');
If you wanted your search to bring back results where the result is prefixed with David, Robi or Moses you could do:
如果您希望搜索返回以 David、Robi 或 Moses 为前缀的结果，您可以执行以下操作：
SELECT *
FROM T
WHERE CONTAINS(C, '"David*" OR "Robi*" OR "Moses*"');
回答by Etay Gudai 972-522-3322-47
Oracle SQL :
甲骨文 SQL：
select *
from MY_TABLE
where REGEXP_LIKE (company , 'Microsodt industry | goglge auto car | oracles database')
- company - is the database column name.
- results - this SQL will show you if company column rows contain one of those companies (OR phrase) please note that : no wild characters are needed, it's built in.
- company - 是数据库列名。
- 结果 - 此 SQL 将显示公司列行是否包含这些公司之一（或短语），请注意：不需要通配符，它是内置的。
more info at : http://www.techonthenet.com/oracle/regexp_like.php
回答by user2662006
Here is what I uses to search for multiple words in multiple columns - SQL server
这是我用来在多列中搜索多个单词的内容 - SQL Server
Hope my answer help someone :) Thanks
希望我的回答对某人有所帮助:) 谢谢
declare @searchTrm varchar(MAX)='one two three ddd 20 30 comment';
--select value from STRING_SPLIT(@searchTrm, ' ') where trim(value)<>''
select * from Bols
WHERE EXISTS (SELECT value
FROM STRING_SPLIT(@searchTrm, ' ')
WHERE
trim(value)<>''
and(
BolNumber like '%'+ value+'%'
or UserComment like '%'+ value+'%'
or RequesterId like '%'+ value+'%' )
)
回答by KIR
If you care about the sequence of the terms, you may consider using a syntax like
如果您关心术语的顺序，您可以考虑使用类似的语法
select * from T where C like'%David%Moses%Robi%'
回答by Rufino
Maybe EXISTScan help.
也许EXISTS可以提供帮助。
and exists (select 1 from @DocumentNames where pcd.Name like DocName+'%' or CD.DocumentName like DocName+'%')
回答by Ovais Khatri
if you put all the searched words in a temporaray table say @tmp and column col1, then you could try this:
如果您将所有搜索到的词放在临时表中说@tmp 和列 col1，那么您可以尝试以下操作：
Select * from T where C like (Select '%'+col1+'%' from @temp);
回答by walt
Oracle SQL:
There is the "IN" Operator in Oracle SQL which can be used for that:
Oracle SQL：Oracle SQL 中
有“IN”运算符，可用于：
select
namet.customerfirstname, addrt.city, addrt.postalcode
from schemax.nametable namet
join schemax.addresstable addrt on addrt.adtid = namet.natadtid
where namet.customerfirstname in ('David', 'Moses', 'Robi');
