The attached patch implements the soundex difference function which
compares two strings' soundex values for similarity.
http://databases.about.com/od/development/l/aasoundex.htm
Kris Jurka
? contrib/fuzzystrmatch/.deps
? contrib/fuzzystrmatch/fuzzystrmatch.sql
? contrib/fuzzystrmatch/libfuzzystrmatch.so.0.0
Index: contrib/fuzzystrmatch/README.fuzzystrmatch
===================================================================
RCS file: /projects/cvsroot/pgsql/contrib/fuzzystrmatch/README.fuzzystrmatch,v
retrieving revision 1.7
diff -c -r1.7 README.fuzzystrmatch
*** contrib/fuzzystrmatch/README.fuzzystrmatch 1 Jan 2005 20:44:11 -0000
1.7
--- contrib/fuzzystrmatch/README.fuzzystrmatch 25 Jan 2005 06:07:11 -0000
***************
*** 33,38 ****
--- 33,42 ----
* Folded existing soundex contrib into this one. Renamed text_soundex() (C
function)
* to soundex() for consistency.
*
+ * difference()
+ * ------------
+ * Return the difference between two strings' soundex values. Kris Jurka
+ *
* 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
Index: contrib/fuzzystrmatch/README.soundex
===================================================================
RCS file: /projects/cvsroot/pgsql/contrib/fuzzystrmatch/README.soundex,v
retrieving revision 1.2
diff -c -r1.2 README.soundex
*** contrib/fuzzystrmatch/README.soundex 1 Jul 2004 03:25:48 -0000
1.2
--- contrib/fuzzystrmatch/README.soundex 25 Jan 2005 06:07:11 -0000
***************
*** 7,21 ****
--- 7,31 ----
beyond English names (or the English pronunciation of names), and
it is not a linguistic tool.
+ When comparing two soundex values to determine similarity, the
+ difference function reports how close the match is on a scale
+ from zero to four, with zero being no match and four being an
+ exact match.
+
The following are some usage examples:
SELECT soundex('hello world!');
+ SELECT soundex('Anne'), soundex('Ann'), difference('Anne', 'Ann');
+ SELECT soundex('Anne'), soundex('Andrew'), difference('Anne', 'Andrew');
+ SELECT soundex('Anne'), soundex('Margaret'), difference('Anne', 'Margaret');
+
CREATE TABLE s (nm text)\g
insert into s values ('john')\g
insert into s values ('joan')\g
insert into s values ('wobbly')\g
+ insert into s values ('jack')\g
select * from s
where soundex(nm) = soundex('john')\g
***************
*** 58,62 ****
WHERE text_sx_eq(nm,'john')\g
SELECT *
! from s
! where s.nm #= 'john';
--- 68,77 ----
WHERE text_sx_eq(nm,'john')\g
SELECT *
! FROM s
! WHERE s.nm #= 'john';
!
! SELECT *
! FROM s
! WHERE difference(s.nm, 'john') > 2;
!
Index: contrib/fuzzystrmatch/fuzzystrmatch.c
===================================================================
RCS file: /projects/cvsroot/pgsql/contrib/fuzzystrmatch/fuzzystrmatch.c,v
retrieving revision 1.14
diff -c -r1.14 fuzzystrmatch.c
*** contrib/fuzzystrmatch/fuzzystrmatch.c 1 Jan 2005 05:43:06 -0000
1.14
--- contrib/fuzzystrmatch/fuzzystrmatch.c 25 Jan 2005 06:07:11 -0000
***************
*** 755,757 ****
--- 755,777 ----
++count;
}
}
+
+ PG_FUNCTION_INFO_V1(difference);
+
+ Datum
+ difference(PG_FUNCTION_ARGS)
+ {
+ char sndx1[SOUNDEX_LEN+1], sndx2[SOUNDEX_LEN+1];
+ int i, result;
+
+ _soundex(_textout(PG_GETARG_TEXT_P(0)), sndx1);
+ _soundex(_textout(PG_GETARG_TEXT_P(1)), sndx2);
+
+ result = 0;
+ for (i=0; i<SOUNDEX_LEN; i++) {
+ if (sndx1[i] == sndx2[i])
+ result++;
+ }
+
+ PG_RETURN_INT32(result);
+ }
Index: contrib/fuzzystrmatch/fuzzystrmatch.h
===================================================================
RCS file: /projects/cvsroot/pgsql/contrib/fuzzystrmatch/fuzzystrmatch.h,v
retrieving revision 1.10
diff -c -r1.10 fuzzystrmatch.h
*** contrib/fuzzystrmatch/fuzzystrmatch.h 1 Jan 2005 05:43:06 -0000
1.10
--- contrib/fuzzystrmatch/fuzzystrmatch.h 25 Jan 2005 06:07:11 -0000
***************
*** 60,65 ****
--- 60,66 ----
extern Datum levenshtein(PG_FUNCTION_ARGS);
extern Datum metaphone(PG_FUNCTION_ARGS);
extern Datum soundex(PG_FUNCTION_ARGS);
+ extern Datum difference(PG_FUNCTION_ARGS);
/*
* Soundex
Index: contrib/fuzzystrmatch/fuzzystrmatch.sql.in
===================================================================
RCS file: /projects/cvsroot/pgsql/contrib/fuzzystrmatch/fuzzystrmatch.sql.in,v
retrieving revision 1.6
diff -c -r1.6 fuzzystrmatch.sql.in
*** contrib/fuzzystrmatch/fuzzystrmatch.sql.in 1 Jul 2004 03:25:48 -0000
1.6
--- contrib/fuzzystrmatch/fuzzystrmatch.sql.in 25 Jan 2005 06:07:11 -0000
***************
*** 19,24 ****
--- 19,28 ----
AS 'MODULE_PATHNAME', 'soundex'
LANGUAGE 'C';
+ CREATE FUNCTION difference(text,text) RETURNS int
+ AS 'MODULE_PATHNAME', 'difference'
+ LANGUAGE 'C';
+
CREATE FUNCTION dmetaphone (text) RETURNS text
LANGUAGE C IMMUTABLE STRICT
AS 'MODULE_PATHNAME', 'dmetaphone';
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
