模糊匹配SQL中的字符串

本文介绍了模糊匹配SQL中的字符串的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个 User 表，该表具有 id ， first_name ，姓氏，街道地址，城市，状态，邮政编码，公司

I have a User table, that has id, first_name, last_name, street_address, city, state, zip-code, firm, user_identifier, created_at, update_at.

此表有很多重复项，例如同一用户已作为新用户多次输入，因此示例

This table has a lot of duplication like the same users have been entered multiple times as a new user, so example

id first_name last_name street_address user_identifier --------------------------------------------------------- 11 Mary Doe 123 Main Ave M2111111 --------------------------------------------------------- 21 Mary Doe 123 Main Ave M2344455 --------------------------------------------------------- 13 Mary Esq Doe 123 Main Ave M1233444

我想知道是否有一种方法可以对此表进行模糊匹配

I would like to know if there is a way of doing fuzzy matching on this table.

基本上，我想找到所有具有相同名称，相同地址但可能会有细微差别的用户，也许地址相同但具有不同

Basically I would like to find all the users that have the same name, same address but can be with a slight difference, maybe the address is the same but has different apartment number, or has a middle name and the other duplicates don't.

我正在考虑创建一个新列，该列将名字，姓氏，街道地址串联在一起，然后对该列进行模糊匹配。

I was thinking to create a new column that has concatenated first_name, last_name, street_address and do a fuzzy match on that column.

我尝试了将first_name和last_name串联为 full_name ，但似乎没有赶上具有中间名的名称

I tried levenshtein distance on concatenated first_name and last_name as full_name but doesn't seem to catch up the name that has a middle name

select * from users where levenshtein('Mary Doe', full_name) <=1;

我正在使用Databricks和PostgreSQL。

I am using a Databricks and PostgreSQL.

谢谢！

推荐答案

在postgres中，您可以使用 fuzzystrmatch 软件包。它提供了 levenshtein 函数，该函数返回两个文本之间的距离，然后可以使用以下示例性谓词执行模糊匹配：

In postgres you can use fuzzystrmatch package. It provies a levenshtein function, that returns distance between two texts, you can then perform fuzzy matching with the following exemplary predicate:

where levenshtein(street_address, '123 Main Avex') <= 1

这将匹配所有记录，因为'123 Main Ave'和'123 Main Avex'之间的距离是1（插入1个）。

This will match all records, because the distance between '123 Main Ave' and '123 Main Avex' is 1 (1 insertion).

Of当然，这里的值 1 只是一个例子，将非常严格地执行匹配（相差一个字符）。您应该使用更大的数字，或者使用@IVO GELOV表示的数字-使用相对距离（距离除以长度）。

Of course, value 1 here is just an example and will perform matching quite strictly (difference by only one character). You should either use larger number or, what @IVO GELOV sugests - use relative distance (distance divided by the length).