fuzzystrmatch
The fuzzystrmatch module provides multiple functions to determine the similarity and distance between strings.
| Caution: Currently, the soundex, metaphone, dmetaphone, and dmetaphone_alt functions do not work well with multibyte encodings. |
|---|
This module is considered "trusted", meaning it can be installed by non-superusers who have CREATE privilege on the current database.
1. Soundex
The Soundex system is a method for matching similar-sounding names by converting them to the same code. This was originally used by the U.S. Census Bureau in 1880, 1900, and 1910. Note that Soundex is not very useful for non-English names.
The fuzzystrmatch module provides two functions for Soundex codes:
soundex(text) returns text difference(text, text) returns int
The soundex function converts a string to its Soundex code.
The difference function converts two strings to their Soundex codes and then reports the number of matching code positions. Since Soundex codes have four characters, the result ranges from zero to four, where zero means no match and four means an exact match (thus the function is poorly named — similarity would be a more appropriate name).
Here are some examples:
test=## CREATE EXTENSION fuzzystrmatch ;
CREATE EXTENSION
test=## SELECT soundex('hello world!');
soundex
---------
H464
(1 row)
test=## SELECT soundex('Anne'), soundex('Ann'), difference('Anne', 'Ann');
soundex | soundex | difference
---------+---------+------------
A500 | A500 | 4
(1 row)
test=## SELECT soundex('Anne'), soundex('Andrew'), difference('Anne', 'Andrew'); SELECT soundex('Anne'), soundex('Margaret'), difference('Anne', 'Margaret');
soundex | soundex | difference
---------+---------+------------
A500 | A536 | 2
(1 row)
soundex | soundex | difference
---------+---------+------------
A500 | M626 | 0
(1 row)
test=## CREATE TABLE s (nm text);
CREATE TABLE
test=## INSERT INTO s VALUES ('john');
INSERT 0 1
test=## INSERT INTO s VALUES ('joan');
INSERT 0 1
test=## INSERT INTO s VALUES ('wobbly');
INSERT 0 1
test=## INSERT INTO s VALUES ('jack');
INSERT 0 1
test=## SELECT * FROM s WHERE soundex(nm) = soundex('john');
nm
------
john
joan
(2 rows)
test=## SELECT * FROM s WHERE difference(s.nm, 'john') > 2;
nm
------
john
joan
jack
(3 rows)
2. Levenshtein
These functions calculate the Levenshtein edit distance between two strings.
levenshtein(text source, text target, int ins_cost, int del_cost, int sub_cost) returns int
levenshtein(text source, text target) returns int
levenshtein_less_equal(text source, text target, int ins_cost, int del_cost, int sub_cost, int max_d) returns int
levenshtein_less_equal(text source, text target, int max_d) returns int
Both source and target can be any non-null string, up to 255 characters in length. The cost parameters specify the cost of a single character insertion, deletion, or substitution, respectively. You can omit the cost parameters as in the second version of the function, in which case they all default to 1.
levenshtein_less_equal is a faster version of the Levenshtein function, intended for use when only small distances are of interest. If the actual distance is less than or equal to max_d, then levenshtein_less_equal returns the correct distance. Otherwise, it returns some value greater than max_d. If max_d is negative, the behavior is identical to levenshtein.
Examples:
test=## SELECT levenshtein('GUMBO', 'GAMBOL');
levenshtein
-------------
2
(1 row)
test=## SELECT levenshtein('GUMBO', 'GAMBOL', 2,1,1);
levenshtein
-------------
3
(1 row)
test=## SELECT levenshtein_less_equal('extensive','exhaustive',2);
levenshtein_less_equal
------------------------
3
(1 row)
test=## SELECT levenshtein_less_equal('extensive','exhaustive',4);
levenshtein_less_equal
------------------------
4
(1 row)
3. Metaphone
Similar to Soundex, the idea behind Metaphone is to construct a code for an input string. If two strings have the same code, they are considered similar.
This function computes the metaphone code for an input string:
metaphone(text source, int max_output_length) returns text
source must be a non-null string with a maximum length of 255 characters. max_output_length sets the maximum length of the output metaphone code; if the output exceeds this length, it will be truncated.
Examples:
test=## SELECT metaphone('GUMBO', 4);
metaphone
-----------
KM
(1 row)
4. Double Metaphone
The Double Metaphone system computes two "sounds-like" strings for a given input string — a "primary" code and an "alternate" code. In most cases they are the same, but for non-English names they may differ slightly depending on pronunciation. These functions compute the primary and alternate codes:
dmetaphone(text source) returns text
dmetaphone_alt(text source) returns text
There is no length limit on the input string.
Examples:
test=## SELECT dmetaphone('gumbo');
dmetaphone
------------
KMP
(1 row)