Received: from mailsys01.intnet.net (mailhost.wwc.com [198.252.32.15]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f77IBB688357 for ; Tue, 7 Aug 2001 14:11:11 -0400 (EDT) (envelope-from joseph.conway@home.com) Received: from [206.19.64.3] (HELO jecw2k1) by mailsys01.intnet.net (CommuniGate Pro SMTP 3.4.8) with SMTP id 12785206; Tue, 07 Aug 2001 14:10:36 -0400 Message-ID: <02d601c11f6c$56da7150$48d210ac@jecw2k1> From: "Joe Conway" To: "Bruce Momjian" Cc: References: <200108071648.f77GmEX20415@candle.pha.pa.us> Subject: Re: Re: Fuzzy matching Date: Tue, 7 Aug 2001 11:11:10 -0700 MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_02D3_01C11F31.AA077AC0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200108/113 X-Sequence-Number: 1533 This is a multi-part message in MIME format. ------=_NextPart_000_02D3_01C11F31.AA077AC0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit > > I have added this into /contrib under fuzzystrmatch. Not sure if we > want to merge soundex into that but it can be done later. > > Sorry - I should have gotten to this sooner. Here's a patch which you should be able to apply against what you just committed. It rolls soundex into fuzzystrmatch. I'm don't think I will multibyte-enable levenshtein right now. If there is some interest in it, I'll do that later. Joe ------=_NextPart_000_02D3_01C11F31.AA077AC0 Content-Type: application/octet-stream; name="fuzzystrmatch_r02.diff" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="fuzzystrmatch_r02.diff" diff -Naur pgsql.alt/contrib/fuzzystrmatch/README.fuzzystrmatch pgsql.dev/c= ontrib/fuzzystrmatch/README.fuzzystrmatch --- pgsql.alt/contrib/fuzzystrmatch/README.fuzzystrmatch Fri Aug 3 12:47:2= 5 2001 +++ pgsql.dev/contrib/fuzzystrmatch/README.fuzzystrmatch Tue Aug 7 11:00:1= 7 2001 @@ -20,6 +20,11 @@ * Metaphone was originally created by Lawrence Philips and presented in a= rticle * in "Computer Language" December 1990 issue. * + * soundex() + * ----------- + * Folded existing soundex contrib into this one. Renamed text_soundex() (= C function) + * to soundex() for consistency. + * * Permission to use, copy, modify, and distribute this software and its * documentation for any purpose, without fee, and without a written agree= ment * is hereby granted, provided that the above copyright notice and this @@ -40,11 +45,14 @@ */ =20 =20 -Version 0.1 (3 August, 2001): +Version 0.2 (7 August, 2001): Functions to calculate the degree to which two strings match in a "fuzzy= " way Tested under Linux (Red Hat 6.2 and 7.0) and PostgreSQL 7.2devel =20 Release Notes: + + Version 0.2 + - folded soundex contrib into this one =20 Version 0.1 - initial release=20=20=20=20 diff -Naur pgsql.alt/contrib/fuzzystrmatch/README.soundex pgsql.dev/contrib= /fuzzystrmatch/README.soundex --- pgsql.alt/contrib/fuzzystrmatch/README.soundex Thu Jan 1 00:00:00 1970 +++ pgsql.dev/contrib/fuzzystrmatch/README.soundex Tue Aug 7 10:42:27 2001 @@ -0,0 +1,62 @@ +NOTE: Modified August 07, 2001 by Joe Conway. Updated for accuracy + after combining soundex code into the fuzzystrmatch contrib +--------------------------------------------------------------------- +The Soundex system is a method of matching similar sounding names +(or any words) to the same code. It was initially used by the +United States Census in 1880, 1900, and 1910, but it has little use +beyond English names (or the English pronunciation of names), and +it is not a linguistic tool. + +The following are some usage examples: + +SELECT soundex('hello world!'); + +CREATE TABLE s (nm text)\g + +insert into s values ('john')\g +insert into s values ('joan')\g +insert into s values ('wobbly')\g + +select * from s +where soundex(nm) =3D soundex('john')\g + +select nm from s a, s b +where soundex(a.nm) =3D soundex(b.nm) +and a.oid <> b.oid\g + +CREATE FUNCTION text_sx_eq(text, text) RETURNS bool AS +'select soundex($1) =3D soundex($2)' +LANGUAGE 'sql'\g + +CREATE FUNCTION text_sx_lt(text,text) RETURNS bool AS +'select soundex($1) < soundex($2)' +LANGUAGE 'sql'\g + +CREATE FUNCTION text_sx_gt(text,text) RETURNS bool AS +'select soundex($1) > soundex($2)' +LANGUAGE 'sql'; + +CREATE FUNCTION text_sx_le(text,text) RETURNS bool AS +'select soundex($1) <=3D soundex($2)' +LANGUAGE 'sql'; + +CREATE FUNCTION text_sx_ge(text,text) RETURNS bool AS +'select soundex($1) >=3D soundex($2)' +LANGUAGE 'sql'; + +CREATE FUNCTION text_sx_ne(text,text) RETURNS bool AS +'select soundex($1) <> soundex($2)' +LANGUAGE 'sql'; + +DROP OPERATOR #=3D (text,text)\g + +CREATE OPERATOR #=3D (leftarg=3Dtext, rightarg=3Dtext, procedure=3Dtext_sx= _eq, +commutator=3Dtext_sx_eq)\g + +SELECT * +FROM s +WHERE text_sx_eq(nm,'john')\g + +SELECT * +from s +where s.nm #=3D 'john'; diff -Naur pgsql.alt/contrib/fuzzystrmatch/fuzzystrmatch.c pgsql.dev/contri= b/fuzzystrmatch/fuzzystrmatch.c --- pgsql.alt/contrib/fuzzystrmatch/fuzzystrmatch.c Fri Aug 3 12:47:25 2001 +++ pgsql.dev/contrib/fuzzystrmatch/fuzzystrmatch.c Tue Aug 7 10:52:40 2001 @@ -629,3 +629,71 @@ =20=09 return(META_SUCCESS); } /* END metaphone */ + + +/* + * SQL function: soundex(text) returns text + */ +PG_FUNCTION_INFO_V1(soundex); + +Datum +soundex(PG_FUNCTION_ARGS) +{ + char outstr[SOUNDEX_LEN + 1]; + char *arg; + + arg =3D _textout(PG_GETARG_TEXT_P(0)); + + _soundex(arg, outstr); + + PG_RETURN_TEXT_P(_textin(outstr)); +} + +static void +_soundex(const char *instr, char *outstr) +{ + int count; + + AssertArg(instr); + AssertArg(outstr); + + outstr[SOUNDEX_LEN] =3D '\0'; + + /* Skip leading non-alphabetic characters */ + while (!isalpha((unsigned char) instr[0]) && instr[0]) + ++instr; + + /* No string left */ + if (!instr[0]) + { + outstr[0] =3D (char) 0; + return; + } + + /* Take the first letter as is */ + *outstr++ =3D (char) toupper((unsigned char) *instr++); + + count =3D 1; + while (*instr && count < SOUNDEX_LEN) + { + if (isalpha((unsigned char) *instr) && + soundex_code(*instr) !=3D soundex_code(*(instr - 1))) + { + *outstr =3D soundex_code(instr[0]); + if (*outstr !=3D '0') + { + ++outstr; + ++count; + } + } + ++instr; + } + + /* Fill with 0's */ + while (count < SOUNDEX_LEN) + { + *outstr =3D '0'; + ++outstr; + ++count; + } +} diff -Naur pgsql.alt/contrib/fuzzystrmatch/fuzzystrmatch.h pgsql.dev/contri= b/fuzzystrmatch/fuzzystrmatch.h --- pgsql.alt/contrib/fuzzystrmatch/fuzzystrmatch.h Fri Aug 3 12:47:25 2001 +++ pgsql.dev/contrib/fuzzystrmatch/fuzzystrmatch.h Tue Aug 7 10:38:46 2001 @@ -51,31 +51,42 @@ #include "utils/builtins.h" =20 =20 -#define MAX_LEVENSHTEIN_STRLEN 255 -#define MAX_METAPHONE_STRLEN 255 - -typedef struct dynmatrix -{ - int value; -} dynmat; - =20 /* * External declarations */ extern Datum levenshtein(PG_FUNCTION_ARGS); extern Datum metaphone(PG_FUNCTION_ARGS); +extern Datum soundex(PG_FUNCTION_ARGS); =20 /* - * Internal declarations + * Soundex */ +static void _soundex(const char *instr, char *outstr); =20 +#define SOUNDEX_LEN 4 +#define _textin(str) DirectFunctionCall1(textin, CStringGetDatum(str)) +#define _textout(str) DatumGetPointer(DirectFunctionCall1(textout, Pointer= GetDatum(str))) =20 +/* ABCDEFGHIJKLMNOPQRSTUVWXYZ */ +static const char *soundex_table =3D "01230120022455012623010202"; =20 +#define soundex_code(letter) soundex_table[toupper((unsigned char) (letter= )) - 'A'] + + +/* + * Levenshtein + */ #define STRLEN(p) strlen(p) #define CHAREQ(p1, p2) (*(p1) =3D=3D *(p2)) #define NextChar(p) ((p)++) +#define MAX_LEVENSHTEIN_STRLEN 255 + =20 +/* + * Metaphone + */ +#define MAX_METAPHONE_STRLEN 255 =20 /*=20 * Original code by Michael G Schwern starts here. diff -Naur pgsql.alt/contrib/fuzzystrmatch/fuzzystrmatch.sql.in pgsql.dev/c= ontrib/fuzzystrmatch/fuzzystrmatch.sql.in --- pgsql.alt/contrib/fuzzystrmatch/fuzzystrmatch.sql.in Fri Aug 3 12:47:2= 5 2001 +++ pgsql.dev/contrib/fuzzystrmatch/fuzzystrmatch.sql.in Tue Aug 7 10:53:4= 9 2001 @@ -3,3 +3,9 @@ =20 CREATE FUNCTION metaphone (text,int) RETURNS text AS 'MODULE_PATHNAME','metaphone' LANGUAGE 'c' with (iscachable, isstrict= ); + +CREATE FUNCTION soundex(text) RETURNS text + AS 'MODULE_PATHNAME', 'soundex' LANGUAGE 'c' with (iscachable, isstrict); + +CREATE FUNCTION text_soundex(text) RETURNS text + AS 'MODULE_PATHNAME', 'soundex' LANGUAGE 'c'; ------=_NextPart_000_02D3_01C11F31.AA077AC0--