Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6VG5Vf56654 for ; Tue, 31 Jul 2001 12:05:31 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 94360 for ; Tue, 31 Jul 2001 09:05:28 -0700 From: "Josh Berkus" Subject: Fuzzy matching? To: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Tue, 31 Jul 2001 09:05:28 -0700 Message-ID: MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="_===94360====davinci.ethosmedia.com===_" X-Archive-Number: 200107/391 X-Sequence-Number: 4205 This is a multi-part MIME message --_===94360====davinci.ethosmedia.com===_ Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit Folks, For many of my programs, it would be extremely useful to have some form of "fuzzy matching" for VARCHAR fields. There are two kinds of fuzzy matching for words that I know of: 1. Phonetic matching, which would be nice but will have to wait for someone's $100,000 project; 2. Textual mathcing, which I will outline below. The way textual fuzzy matching should work is as follows: The developer supplies two VARCHARs to match and a number/percent of character mis-match that is acceptable: Fuzzy_match('Thornton','Tornton',1) And the fuzzy_match should return True if the two phrases are no more than that number of characters different. Thus, we should get: fuzzy_match('Thornton','Tornton',1) = TRUE fuzzy_match('Thornton','Torntin',1) = FALSE fuzzy_match('Thornton','Torntin',2) = TRUE Unfortunately, I cannot think of a way to make this happen in a function without cycling through all the possible permutations of characters for both words or doing some character-by-character comparison with elaborate logic for placement. Either of these approaches would be very slow, and completely unsuitable for column comparisons on large tables. Can anyone suggest some shortcuts here? Perhaps using pl/perl or something similar? Grazie! -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco --_===94360====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===94360====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===94360====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===94360====davinci.ethosmedia.com===_--