在PostgreSQL中如何结合NOT IN和LIKE? [英] In PostgreSQL how to combine NOT IN and LIKE?
问题描述
如何结合NOT IN
和LIKE
?
让我们假设我们有一个包含一列名称(例如蓝纹奶酪","gouda奶酪"之类的名称)的表,而我想选择所有不包含"cheese","milk"的名称','肉'.
Let's assume we have a table that contains a column of names (something like 'blue cheese', 'gouda cheese' and so on) and I want to select all the names that doesn't contain 'cheese', 'milk', 'meat'.
据我了解,要查找不在字符串数组中的内容,请使用NOT IN
并传递字符串
As far as I understand to look for something that is not in an array of strings you use NOT IN
and the pass the strings
SELECT names FROM some_table NOT IN('cheese','milk','meat');
但是我如何通过
LIKE '%cheese%'
要吗?
推荐答案
构造LIKE ANY (ARRAY[...])
似乎可以满足您的需求;
The construct LIKE ANY (ARRAY[...])
appears to meet your needs;
craig=> SELECT a FROM (
VALUES ('cheesy'), ('imilk'), ('donut'), ('pie'), ('avocado'), ('meaty')
) x(a)
WHERE NOT a LIKE ANY (ARRAY['%cheese%','%milk%','%meat%']);
a
---------
cheesy
donut
pie
avocado
(4 rows)
如果要以这种方式使用LIKE
,则需要通配符.如果您真的只想平等,可以使用:
You need the wildcard characters if you want to use LIKE
this way. If you really just want equality, you can use:
NOT = ANY (...)
这篇关于在PostgreSQL中如何结合NOT IN和LIKE?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!