获取具有相似声音的记录 [英] Get records with similar sounds

问题描述

我想从联系人表中找到所有重复的姓名，这些姓名的名称在声音上是匹配的.例如:Rita或Reeta，Microsoft或Microsift，Mukherjee或Mukherji.

I want to find all duplicate names from contacts table whose name matches by sound. Eg : Rita or Reeta , Microsoft or Microsift , Mukherjee or Mukherji.

我已使用以下查询:

SELECT contacts.id 
FROM contacts 
INNER JOIN (
    SELECT first_name, last_name, count(*) AS rows 
    FROM contacts 
    WHERE deleted = 0 
    GROUP BY SOUNDEX(first_name), SOUNDEX(last_name) 
    HAVING count(rows) > 1
) AS p 
WHERE contacts.deleted = 0 
AND p.first_name SOUNDS LIKE contacts.first_name 
AND p.last_name SOUNDS LIKE contacts.last_name 
ORDER BY contacts.date_entered DESC

上面的查询给出了正确的结果，但是当有很多记录时会花费很多时间.

The above query gives right results but takes a lot of time when there are many records.

推荐答案

SOUNDEX(IMHO)的实用程序非常有限.一个极端的例子...

SOUNDEX is (IMHO) of very limited utility. An extreme example...

SELECT SOUNDEX('cholmondley');
+------------------------+
| SOUNDEX('cholmondley') |
+------------------------+
| C4534                  |
+------------------------+

SELECT SOUNDEX('chumleigh');
+----------------------+
| SOUNDEX('chumleigh') |
+----------------------+
| C542                 |
+----------------------+

这篇关于获取具有相似声音的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！