PostgreSQL反向喜欢 [英] PostgreSQL Reverse LIKE
问题描述
我需要测试列值的任何部分是否在给定的字符串中，而不是字符串是否是列值的一部分。
例如：
I need to test if any part of a column value is in a given string, instead of whether the string is part of a column value. For instance:
这样，我可以发现表中的任何行在列中是否包含字符串 bricks ：
This way, I can find if any of the rows in my table contains the string 'bricks' in column:
SELECT column FROM table
WHERE column ILIKE '%bricks%';
但是我要查找的是找出句子 船在空中悬挂的方式几乎与砖在任何行中都没有的方式相同。
像这样的东西：
But what I'm looking for, is to find out if any part of the sentence "The ships hung in the sky in much the same way that bricks don’t" is in any of the rows. Something like:
SELECT column FROM table
WHERE 'The ships hung in the sky in much the same way that bricks don’t' ILIKE '%' || column || '%';
因此第一个示例中的行（其中列包含砖）将作为结果显示。
So the row from the first example, where the column contains 'bricks', will show up as result.
我在这里和其他一些论坛中浏览了一些建议，但没有一个起作用。
I've looked through some suggestions here and some other forums but none of them worked.
推荐答案
使用 ANY 构造和〜* ：
SELECT *
FROM tbl
WHERE col ~* ANY (string_to_array('The ships hung in the sky ... bricks don’t', ' '));
〜* 是不区分大小写的正则表达式匹配运算符。我用它代替了 ILIKE ，所以我们可以在字符串中使用原始单词，而无需用％填充 ILIKE 。结果相同-除了包含特殊字符的单词： ILIKE 和<$ c $的％_ \ c>！$（）* +。：< =>？[\] ^ {|}-用于正则表达式模式。您可能需要以任何一种方式转义特殊字符，以免出现意外。这是一个用于正则表达式的函数：
~* is the case insensitive regular expression match operator. I use that instead of ILIKE so we can use original words in your string without the need to pad % for ILIKE. The result is the same - except for words containing special characters: %_\ for ILIKE and !$()*+.:<=>?[\]^{|}- for regular expression patterns. You may need to escape special characters either way to avoid surprises. Here is a function for regular expressions:
- Escape function for regular expression or LIKE patterns
但是我na惑不解，这将是您所需要的。看到我的评论。我怀疑您需要全文搜索和自然语言匹配的词典才能提供有用的词干...
But I have nagging doubts that will be all you need. See my comment. I suspect you need Full Text Search with a matching dictionary for your natural language to provide useful word stemming ...
相关：
- IN vs ANY operator in PostgreSQL
- PostgreSQL LIKE query performance variations
- Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL
这篇关于PostgreSQL反向喜欢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
