如何在sql server中使用like运算符选择匹配百分比高于其他的记录? [英] how to select record whose matching percentage is higher than other using like operator in sql server?

问题描述

我有一组需要使用条件进行搜索的记录.但是标准返回了多行.

I have set of records which I need to search using criteria. But criteria is returning me multiple rows.

所以我需要前 2 条记录符合条件匹配的最大百分比.

So I need top 2 records which are having maximum percentage of criteria matching.

我研究过模糊逻辑，但发现对于如此简单的问题来说它太复杂了.我有如下场景:

I worked on fuzzy logic but found that it is too complex for such simple problems. I have scenarios like below:

SELECT DISTINCT FirstName, LastName, CountryName, StateName FROM Employee

例如上面的一个返回给我 5 条记录.

Say for example above one is returning me 5 records.

我想要的是使用like"操作符，通过它我可以找到像 '%Gujarat%' & 这样的州名.countryname like '%India%' 与以上五个记录的匹配百分比.

What I want is like use "like" operator thru which I can find that statename like '%Gujarat%' & countryname like '%India%' matching percentage with above five records.

一旦我得到这个匹配百分比，我将选择匹配百分比最高的前 2 条记录.

Once I got this matching percentage, I will select top 2 records with highest amount of matching percentage.

这会让我得到一些准确的数据.

This will lead me to get somewhat accurate data.

知道使用 sql server 吗?

Any idea using sql server?

推荐答案

据我所知你需要类似 使用 Levenshtein 距离算法的模糊字符串匹配.希望链接对您有所帮助.

As far as I understand you need something like Fuzzy String Matching using Levenshtein Distance Algorithm. Hope the link will be helpful.

您需要计算 CountryName 和搜索模式之间的距离.它不完全是百分比"，但它可以衡量相关性.

You need to calculate distance between CountryName and search pattern. It's not exactly the "percentage", but it can measure the relevance.

也许这可以解决您的问题?

Maybe this solves your problem?

SELECT TOP 2 FirstName, LastName, CountryName, StateName 
FROM Employee
WHERE
    statename like '%Gujarat%' AND countryname like '%India%'
ORDER BY
    dbo.edit_distance(statename, 'Gujarat') + dbo.edit_distance(CountryName, 'India') DESC

这篇关于如何在sql server中使用like运算符选择匹配百分比高于其他的记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！