pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Joe Conway <joseph.conway@home.com>
To: Bruce Momjian <pgman@candle.pha.pa.us>
Cc: pgsql-patches@postgresql.org
Subject: Re: Re: Fuzzy matching
Date: Tue, 7 Aug 2001 11:11:10 -0700
Message-ID: <02d601c11f6c$56da7150$48d210ac@jecw2k1> (raw)
References: <200108071648.f77GmEX20415@candle.pha.pa.us>

>
> 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

Attachments:

fuzzystrmatch_r02.diff (application/octet-stream, 6.5K) download diff -Naur pgsql.alt/contrib/fuzzystrmatch/README.fuzzystrmatch pgsql.dev/contrib/fuzzystrmatch/README.fuzzystrmatch --- pgsql.alt/contrib/fuzzystrmatch/README.fuzzystrmatch Fri Aug 3 12:47:25 2001 +++ pgsql.dev/contrib/fuzzystrmatch/README.fuzzystrmatch Tue Aug 7 11:00:17 2001 @@ -20,6 +20,11 @@ * Metaphone was originally created by Lawrence Philips and presented in article * 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 agreement * is hereby granted, provided that the above copyright notice and this @@ -40,11 +45,14 @@ */ -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 Release Notes: + + Version 0.2 + - folded soundex contrib into this one Version 0.1 - initial release 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) = soundex('john')\g + +select nm from s a, s b +where soundex(a.nm) = soundex(b.nm) +and a.oid <> b.oid\g + +CREATE FUNCTION text_sx_eq(text, text) RETURNS bool AS +'select soundex($1) = 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) <= soundex($2)' +LANGUAGE 'sql'; + +CREATE FUNCTION text_sx_ge(text,text) RETURNS bool AS +'select soundex($1) >= soundex($2)' +LANGUAGE 'sql'; + +CREATE FUNCTION text_sx_ne(text,text) RETURNS bool AS +'select soundex($1) <> soundex($2)' +LANGUAGE 'sql'; + +DROP OPERATOR #= (text,text)\g + +CREATE OPERATOR #= (leftarg=text, rightarg=text, procedure=text_sx_eq, +commutator=text_sx_eq)\g + +SELECT * +FROM s +WHERE text_sx_eq(nm,'john')\g + +SELECT * +from s +where s.nm #= 'john'; diff -Naur pgsql.alt/contrib/fuzzystrmatch/fuzzystrmatch.c pgsql.dev/contrib/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 @@ 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 = _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] = '\0'; + + /* Skip leading non-alphabetic characters */ + while (!isalpha((unsigned char) instr[0]) && instr[0]) + ++instr; + + /* No string left */ + if (!instr[0]) + { + outstr[0] = (char) 0; + return; + } + + /* Take the first letter as is */ + *outstr++ = (char) toupper((unsigned char) *instr++); + + count = 1; + while (*instr && count < SOUNDEX_LEN) + { + if (isalpha((unsigned char) *instr) && + soundex_code(*instr) != soundex_code(*(instr - 1))) + { + *outstr = soundex_code(instr[0]); + if (*outstr != '0') + { + ++outstr; + ++count; + } + } + ++instr; + } + + /* Fill with 0's */ + while (count < SOUNDEX_LEN) + { + *outstr = '0'; + ++outstr; + ++count; + } +} diff -Naur pgsql.alt/contrib/fuzzystrmatch/fuzzystrmatch.h pgsql.dev/contrib/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" -#define MAX_LEVENSHTEIN_STRLEN 255 -#define MAX_METAPHONE_STRLEN 255 - -typedef struct dynmatrix -{ - int value; -} dynmat; - /* * External declarations */ extern Datum levenshtein(PG_FUNCTION_ARGS); extern Datum metaphone(PG_FUNCTION_ARGS); +extern Datum soundex(PG_FUNCTION_ARGS); /* - * Internal declarations + * Soundex */ +static void _soundex(const char *instr, char *outstr); +#define SOUNDEX_LEN 4 +#define _textin(str) DirectFunctionCall1(textin, CStringGetDatum(str)) +#define _textout(str) DatumGetPointer(DirectFunctionCall1(textout, PointerGetDatum(str))) +/* ABCDEFGHIJKLMNOPQRSTUVWXYZ */ +static const char *soundex_table = "01230120022455012623010202"; +#define soundex_code(letter) soundex_table[toupper((unsigned char) (letter)) - 'A'] + + +/* + * Levenshtein + */ #define STRLEN(p) strlen(p) #define CHAREQ(p1, p2) (*(p1) == *(p2)) #define NextChar(p) ((p)++) +#define MAX_LEVENSHTEIN_STRLEN 255 + +/* + * Metaphone + */ +#define MAX_METAPHONE_STRLEN 255 /* * Original code by Michael G Schwern starts here. diff -Naur pgsql.alt/contrib/fuzzystrmatch/fuzzystrmatch.sql.in pgsql.dev/contrib/fuzzystrmatch/fuzzystrmatch.sql.in --- pgsql.alt/contrib/fuzzystrmatch/fuzzystrmatch.sql.in Fri Aug 3 12:47:25 2001 +++ pgsql.dev/contrib/fuzzystrmatch/fuzzystrmatch.sql.in Tue Aug 7 10:53:49 2001 @@ -3,3 +3,9 @@ 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';

view thread (26+ messages)  latest in thread

Message-ID: <02d601c11f6c$56da7150$48d210ac@jecw2k1>
Permalink:  ../02d601c11f6c$56da7150$48d210ac@jecw2k1/
Also on:    postgresql.org/message-id/02d601c11f6c$56da7150$48d210ac@jecw2k1

 ·  · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: joseph.conway@home.com, pgman@candle.pha.pa.us, pgsql-patches@postgresql.org
  Subject: Re: Re: Fuzzy matching
  In-Reply-To: <02d601c11f6c$56da7150$48d210ac@jecw2k1>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox