How to use the string similarity feature in ApsaraDB RDS for PostgreSQL - ApsaraDB RDS

ApsaraDB RDS for PostgreSQL provides the fuzzystrmatch extension. This extension supports the Soundex, Levenshtein, Metaphone, and Double Metaphone algorithms. You can use these algorithms to calculate the similarity and distance between strings.

Enable or disable the fuzzystrmatch extension

Enable the extension.
```
CREATE EXTENSION fuzzystrmatch;
```
Disable the extension.
```
DROP EXTENSION fuzzystrmatch;
```

Soundex

The Soundex algorithm converts similar-sounding words into the same code. However, this algorithm is unsuitable for non-English words.

The Soundex algorithm provides the following functions:

soundex(text) returns text
difference(text, text) returns int

The soundex function converts a string to its Soundex code, such as A550.
The difference function converts two strings to their Soundex codes and returns the number of matching code positions. Because Soundex codes have four characters, the result ranges from 0 to 4. A value of 0 indicates no match, and 4 indicates a perfect match.

Examples:

SELECT soundex('hello world!');
SELECT soundex('Anne'), soundex('Andrew'), difference('Anne', 'Andrew');
SELECT soundex('Anne'), soundex('Margaret'), difference('Anne', 'Margaret');
CREATE TABLE s (nm text);
INSERT INTO s VALUES ('john');
INSERT INTO s VALUES ('joan');
INSERT INTO s VALUES ('wobbly');
INSERT INTO s VALUES ('jack');
SELECT * FROM s WHERE soundex(nm) = soundex('john');
SELECT * FROM s WHERE difference(s.nm, 'john') > 2;

Levenshtein

The Levenshtein algorithm calculates the Levenshtein distance between two strings.

The Levenshtein algorithm provides the following functions:

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

The following table describes the parameters that you must configure in the preceding functions.

Parameter	Description
source	The first string. It cannot be empty and can be up to 255 characters long.
target	The second string. It cannot be empty and can be up to 255 characters long.
ins_cost	The cost of a character insertion.
del_cost	The cost of a character deletion.
sub_cost	The cost of a character substitution.
max_d	The maximum Levenshtein distance.

Note

The levenshtein_less_equal function is an optimized version of the levenshtein function, useful when checking small distances:

If the actual distance is less than or equal to max_d, the function returns the exact distance.
If the actual distance is greater than max_d, the function returns a value greater than max_d.
If max_d is negative, the function behaves identically to the levenshtein function.

Examples:

SELECT levenshtein('GUMBO', 'GAMBOL');
SELECT levenshtein('GUMBO', 'GAMBOL', 2,1,1);
SELECT levenshtein_less_equal('extensive', 'exhaustive',2);
SELECT levenshtein_less_equal('extensive', 'exhaustive',4);

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)

Metaphone

The Metaphone algorithm works in the same way as the Soundex algorithm. The Metaphone algorithm constructs a representative code for each specified string. If two strings have the same representative code, the Metaphone algorithm considers them to be similar.

The Metaphone algorithm provides the following functions:

metaphone(text source, int max_output_length) returns text

The following table describes the parameters that you must configure in the preceding functions.

Parameter	Description
source	The source string. It cannot be empty and can be up to 255 characters long.
max_output_length	The maximum length of the output metaphone code. Longer codes are truncated.

Example:

SELECT metaphone('GUMBO', 4);

Double Metaphone

The Double Metaphone algorithm obtains two similar-sounding codes for a specified string. These codes include a primary code and a secondary code. In most cases, the two codes are the same. They may be slightly different when you specify a non-English word. The difference varies based on the pronunciation.

The Double Metaphone algorithm provides the following functions:

dmetaphone(text source) returns text
dmetaphone_alt(text source) returns text

Examples:

select dmetaphone('gumbo');
select dmetaphone_alt('gumbo');