如何仅使用Postgresql创建简单的模糊搜索？ [英] How to create simple fuzzy search with Postgresql only?

问题描述

我在基于RoR的网站上的搜索功能有点问题。我有许多带有某些代码的产品。此代码可以是任何字符串，例如 AB-123-lHdfj。现在，我使用ILIKE运算符查找产品：

I have a little problem with search functionality on my RoR based site. I have many Produts with some CODEs. This code can be any string like "AB-123-lHdfj". Now I use ILIKE operator to find products:

Product.where("code ILIKE ?", "%" + params[:search] + "%")

它可以正常工作，但是找不到带有以下代码的产品

It works fine, but it can't find product with codes like "AB123-lHdfj", or "AB123lHdfj".

我应该怎么做？可能是postgresql具有一些字符串归一化功能，或者其他一些方法可以帮助我吗？ ：）

What should I do for this? May be postgresql has some string normalization function, or some other methods to help me? :)

推荐答案

Postgres为模块提供了多个字符串比较功能，例如soundex和metaphone。但是，您将需要使用左手边编辑距离功能。

Postgres provides a module with several string comparsion functions such as soundex and metaphone. But you will want to use the levenshtein edit distance function.

Example:

test=# SELECT levenshtein('GUMBO', 'GAMBOL');
 levenshtein
-------------
           2
(1 row)

2 是两个单词之间的编辑距离。当您将其应用于多个单词并按编辑距离结果进行排序时，您将具有要查找的模糊匹配类型。

The 2 is the edit distance between the two words. When you apply this against a number of words and sort by the edit distance result you will have the type of fuzzy matches that you're looking for.

尝试此查询示例：（当然还有您自己的对象名称和数据）

Try this query sample: (with your own object names and data of course)

SELECT * 
FROM some_table
WHERE levenshtein(code, 'AB123-lHdfj') <= 3
ORDER BY levenshtein(code, 'AB123-lHdfj')
LIMIT 10

此查询说：

请给我some_table中所有数据的前10个结果，其中代码值并且输入的'AB123-lHdfj'小于3。您将返回代码值与'AB123-lHdfj'相差3个字符以内的所有行...

Give me the top 10 results of all data from some_table where the edit distance between the code value and the input 'AB123-lHdfj' is less than 3. You will get back all rows where the value of code is within 3 characters difference to 'AB123-lHdfj'...

注意：如果出现类似以下错误：

Note: if you get an error like:

function levenshtein(character varying, unknown) does not exist

使用以下命令安装 fuzzystrmatch 扩展名：

Install the fuzzystrmatch extension using:

test=# CREATE EXTENSION fuzzystrmatch;

这篇关于如何仅使用Postgresql创建简单的模糊搜索？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！