通过名称在SQL中进行模糊匹配 [英] Fuzzy matching in SQL through name

问题描述

大家好，



我正在创建一个程序，我需要匹配相似的名字才能得到结果。

现在这里的问题是名称可以拼写不同或者可以按不同的顺序排列。

我基本上将酒店名称匹配在一起，例如，

有一家酒店马里奥特的。现在这个名字可以拼写不同，因为酒店位于不同的国家，因此每个国家可能有不同的组合，比如在迪拜，它被称为 Mariott Hotel，Dubai ，

在澳大利亚，它被称为 Hotel Mariott，澳大利亚，或者在某处拼写为 t ，如 Mariot Hotel。

实际上，程序应匹配以上所有示例并显示结果。那么我怎样才能用SQL实现这个目标。

需要你的帮助。

我非常感谢。



非常感谢



我尝试了什么：



试着搜索网络但无济于事

解决方案

这是一个棘手的问题 - 因为你提供的许多例子对于人类来说都是显而易见的 - 但对于机器而言却不那么明显。但是这里有一些可能会有所帮助的想法：

  SELECT  
 SoundEx（'  Mariott'），
 SoundEx（'  Mariot'）

给出了相同的结果M630。您需要安装MDS（值得检查您需要的，因为我不是专家）才能使用它。你会得到一些误报和不匹配 - 但这对你的问题很容易。



对于单词订单问题（SoundEx只对拼写有帮助）变化和声音像问题），我建议只是将酒店名称拆分为它的组成单词并在每个单独运行soundEx。



希望这会有所帮助，



Jon

使用< a href =https://msdn.microsoft.com/en-us/library/ms179859.aspx> LIKE（Transact-SQL） [ ^ ]，例如

 WHERE hotelname LIKE'％Mariot％'

Hello all,

I am creating a program where i need to match similar names in order to get the results.
Now the problem here is the names can be spelled differently or can be in different order.
I am basically matching hotel names together and lets say for example,
there is one hotel Mariott. Now this name can be spelled differently and since the hotel is in different countries therefore every country might have a different combination, lets say for example in Dubai it is known as Mariott Hotel, Dubai,
in Australia, it is known as Hotel Mariott, Australia, or somewhere it is spelled with one t, like Mariot Hotel.
In actual the program should match all above examples and display the results. So how can i achieve this using SQL.
Need your help.
I would highly appreciate it.

Many Thanks

What I have tried:

Tried searching the web but to no avail

解决方案

It's a tough problem - because many of the examples you give are obvious for humans - but less so for machines. But here are some thoughts that may help:

SELECT
  SoundEx( 'Mariott' ),
  SoundEx( 'Mariot' )

gives the same results "M630". You need MDS installed (worth checking what you need as I'm no expert) to use this. You'll get some false positives and non-matches - but it's a pretty easy starter for your problem.

For the word order issue (SoundEx will help only with spelling variations and sounds like problems), I would suggest just splitting the hotel name into it's component words and running soundEx on each separately.

Hope this helps,

Jon

Use LIKE (Transact-SQL)[^], e.g.

WHERE hotelname LIKE '%Mariot%'

这篇关于通过名称在SQL中进行模糊匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！