Postgresql 查询以搜索多个字符串的文本字段

Postgresql query to search text field for multiple strings

提问by Huuuze

I have a table that contains a text field (summary). I recently received a request to query that field for any occurrences of a long list of strings. For example, I'd like to search the "summary" field for anyoccurrences of these strings:

我有一个包含文本字段（摘要）的表。我最近收到一个请求，要求查询该字段是否出现一长串字符串。例如，我想在“摘要”字段中搜索这些字符串的任何出现：

• apple
• banana
• carrot
• dog
• elephant
• ... x 50 more items
• zebra

• 苹果
• 香蕉
• 胡萝卜
• 狗
• 大象
• ... x 50 多个项目
• 斑马

Is there a simple query I can write that would return the IDs of the rows that contain any of these values?

是否有我可以编写的简单查询来返回包含任何这些值的行的 ID？

回答by Gordon Linoff

You could put them in one giant regular expression:

你可以把它们放在一个巨大的正则表达式中：

select t.*
from t
where summary similar to '%((apple)|(banana)|(carrot)|(dog)| . . .|(zebra))%';

An alternative, if you want more flexibility and care even less about performances:

另一种选择，如果您想要更多的灵活性并且更不关心性能：

with tosearch as (
      select '%apple%' as pattern union all
      . . .
      select '%zebra%'
     )
select t.*
from t join
     tosearch
     on t.summary like tosearch.pattern;

回答by Tobia Zambon

The simplest way that comes in my mind is something like this:

我想到的最简单的方法是这样的：

SELECT "ID" FROM table WHERE "summary" IN ('apple', 'banana', 'carrot', 'dog', ....)

Try this out.

试试这个。

EDIT AFTER COMMENT:use the similar tooperator:

评论后编辑：使用类似于运算符：

select "ID" from table where upper("summary") similar to upper('%((apple)|(banana)|...|(zebra))%')

回答by Bohemian

Use regex:

使用正则表达式：

select * from mytable
where summary similar to '%(apple|banana|carrot|dog)%'

See SQLFiddleof this working

请参阅此工作的SQLFiddle