获取字符串与SQL Server记录的最佳匹配值 [英] Get best matching value of a string to SQL server record

问题描述

您好！



我有一个名单列表，我想与我的数据库进行比较，以查看是否有任何结果具有最高百分比的字符与已存在于数据库中的记录匹配。例如：我希望在提供名称Fred Pazas时检查谁是最近的匹配记录，并将其与我已经拥有的名为CNAME的数据库列进行比较以下记录：



Wilfred Pazas

Elfred Pastzia

Fredy Pastrazas



我希望SQL Query能够将Wilfred Paza和Fredy Pastraza的前2个记录作为最接近的命中（我相信）与匹配的字符百分比一起返回每条记录的系统。



我尝试过：



我尝试过charindex比较以及子串函数，但结果有点不是我打算返回的方式。我希望你能帮助我找到解决这个问题集的方法，因为我非常需要这个为我正在修改的简单应用程序工作，以便与我的组织成员共享有抱负的程序员。非常感谢，并提前祝福：）

解决方案

查看通过名称在SQL中模糊匹配 [ ^ ]

如果你有一个旧版本的SQL，你必须写你的虽然有自己的Soundex功能。



对于最佳匹配，我会计算 Levenshtein距离 [ ^ ]在您的数据库值和输入值。距离0表示精确，然后数字越小匹配越好。有一个CLR的例子可以在 Levenshtein距离算法 - SQLServerCentral [ ^ ]

我担心你的问题的解决方案没有集成在SQL中，也可能没有集成在任何其他编程语言中。

Quote：

我已经尝试过charindex比较以及子串函数，但结果有点像我打算返回的方式。

首先，你需要为问题找到正确的算法;看看

编辑距离 - 维基百科 [ ^ ]

diff - Wikipedia [ ^ ]

Hi There!

I have a list of names that I want to compare to my database to see if there were any results that has the highest percentage of characters matched from the records already present in the database. For example: I wish to check who is the nearest matching record when I supply the name "Fred Pazas" and compare it with my database column named "CNAME" which already has the following records :

Wilfred Pazas
Elfred Pastzia
Fredy Pastrazas

I wish for the SQL Query to return the top 2 record of Wilfred Paza and Fredy Pastraza as the nearest hit ( I believe ) with the percentage of characters that was matched by the system per record.

What I have tried:

I have tried charindex comparison as well as substring function but results are kinda not the way I intended it to return. I am hoping you could help me come up with a solution to this problem set as I badly needed this to work for the simple application I am currently tinkering with in order to share with my organization's members composed of aspiring programmers. Many thanks in advance and have a blessed day ahead :)

解决方案

Have a look at the solutions at Fuzzy matching in SQL through name[^]
If you have an older version of SQL you will have to write your own Soundex function though.

For "best matching" I would calculate the Levenshtein Distance[^] between your database values and the input value. A distance of 0 means exact, then the smaller the number the better the match. There is an example of a CLR to do that at Levenshtein Distance Algorithm – SQLServerCentral[^]

I fear solution to your problem is not integrated in SQL, and probably not in any other programming language.

Quote:

I have tried charindex comparison as well as substring function but results are kinda not the way I intended it to return.

First, you need to find the correct algorithm for the problem; Have a look at
Edit distance - Wikipedia[^]
diff - Wikipedia[^]

这篇关于获取字符串与SQL Server记录的最佳匹配值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！