SQL 任何单词列表的 PostgreSQL 通配符 LIKE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4928054/
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
PostgreSQL wildcard LIKE for any of a list of words
提问by chmullig
I have a simple list of ~25 words. I have a varchar field in PostgreSQL, let's say that list is ['foo', 'bar', 'baz']. I want to find any row in my table that has any of those words. This will work, but I'd like something more elegant.
我有一个大约 25 个单词的简单列表。我在 PostgreSQL 中有一个 varchar 字段，假设列表是['foo', 'bar', 'baz']. 我想在我的表中找到任何包含这些词的行。这会起作用，但我想要更优雅的东西。
select *
from table
where (lower(value) like '%foo%' or lower(value) like '%bar%' or lower(value) like '%baz%')
回答by mu is too short
PostgreSQL also supports full POSIX regular expressions:
PostgreSQL 还支持完整的POSIX 正则表达式：
select * from table where value ~* 'foo|bar|baz';
The ~*is for a case insensitive match, ~is case sensitive.
该~*是不区分大小写的匹配，~是区分大小写的。
Another option is to use ANY:
另一种选择是使用ANY：
select * from table where value like any (array['%foo%', '%bar%', '%baz%']);
select * from table where value ilike any (array['%foo%', '%bar%', '%baz%']);
You can use ANY with any operator that yields a boolean. I suspect that the regex options would be quicker but ANY is a useful tool to have in your toolbox.
您可以将 ANY 与产生布尔值的任何运算符一起使用。我怀疑正则表达式选项会更快，但 ANY 是您工具箱中的有用工具。
回答by Nordic Mainframe
You can use Postgres' SIMILAR TOoperator which supports alternations, i.e.
您可以使用SIMILAR TO支持交替的Postgres运算符，即
select * from table where lower(value) similar to '%(foo|bar|baz)%';
回答by jlandercy
Actually there is an operator for that in PostgreSQL:
实际上在 PostgreSQL 中有一个操作符：
SELECT *
FROM table
WHERE lower(value) ~~ ANY('{%foo%,%bar%,%baz%}');
回答by Kalendae
One 'elegant' solution would be to use full text search: http://www.postgresql.org/docs/9.0/interactive/textsearch.html. Then you would use full text search queries.
一种“优雅”的解决方案是使用全文搜索：http: //www.postgresql.org/docs/9.0/interactive/textsearch.html。然后您将使用全文搜索查询。
