<?xml version="1.0" encoding="UTF-8" standalone="no"?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>F.15. fuzzystrmatch</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="file-fdw.html" title="F.14. file_fdw" /><link rel="next" href="hstore.html" title="F.16. hstore" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">F.15. fuzzystrmatch</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="file-fdw.html" title="F.14. file_fdw">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules">Up</a></td><th width="60%" align="center">Appendix F. Additional Supplied Modules</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 11.11 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="hstore.html" title="F.16. hstore">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="FUZZYSTRMATCH"><div class="titlepage"><div><div><h2 class="title" style="clear: both">F.15. fuzzystrmatch</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="fuzzystrmatch.html#id-1.11.7.24.5">F.15.1. Soundex</a></span></dt><dt><span class="sect2"><a href="fuzzystrmatch.html#id-1.11.7.24.6">F.15.2. Levenshtein</a></span></dt><dt><span class="sect2"><a href="fuzzystrmatch.html#id-1.11.7.24.7">F.15.3. Metaphone</a></span></dt><dt><span class="sect2"><a href="fuzzystrmatch.html#id-1.11.7.24.8">F.15.4. Double Metaphone</a></span></dt></dl></div><a id="id-1.11.7.24.2" class="indexterm"></a><p> The <code class="filename">fuzzystrmatch</code> module provides several functions to determine similarities and distance between strings. </p><div class="caution"><h3 class="title">Caution</h3><p> At present, the <code class="function">soundex</code>, <code class="function">metaphone</code>, <code class="function">dmetaphone</code>, and <code class="function">dmetaphone_alt</code> functions do not work well with multibyte encodings (such as UTF-8). </p></div><div class="sect2" id="id-1.11.7.24.5"><div class="titlepage"><div><div><h3 class="title">F.15.1. Soundex</h3></div></div></div><p> The Soundex system is a method of matching similar-sounding names by converting them to the same code. It was initially used by the United States Census in 1880, 1900, and 1910. Note that Soundex is not very useful for non-English names. </p><p> The <code class="filename">fuzzystrmatch</code> module provides two functions for working with Soundex codes: </p><a id="id-1.11.7.24.5.4" class="indexterm"></a><a id="id-1.11.7.24.5.5" class="indexterm"></a><pre class="synopsis"> soundex(text) returns text difference(text, text) returns int </pre><p> The <code class="function">soundex</code> function converts a string to its Soundex code. The <code class="function">difference</code> 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, with zero being no match and four being an exact match. (Thus, the function is misnamed — <code class="function">similarity</code> would have been a better name.) </p><p> Here are some usage examples: </p><pre class="programlisting"> 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); 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; </pre></div><div class="sect2" id="id-1.11.7.24.6"><div class="titlepage"><div><div><h3 class="title">F.15.2. Levenshtein</h3></div></div></div><p> This function calculates the Levenshtein distance between two strings: </p><a id="id-1.11.7.24.6.3" class="indexterm"></a><a id="id-1.11.7.24.6.4" class="indexterm"></a><pre class="synopsis"> 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 </pre><p> Both <code class="literal">source</code> and <code class="literal">target</code> can be any non-null string, with a maximum of 255 characters. The cost parameters specify how much to charge for a character insertion, deletion, or substitution, respectively. You can omit the cost parameters, as in the second version of the function; in that case they all default to 1. </p><p> <code class="function">levenshtein_less_equal</code> is an accelerated version of the Levenshtein function for use when only small distances are of interest. If the actual distance is less than or equal to <code class="literal">max_d</code>, then <code class="function">levenshtein_less_equal</code> returns the correct distance; otherwise it returns some value greater than <code class="literal">max_d</code>. If <code class="literal">max_d</code> is negative then the behavior is the same as <code class="function">levenshtein</code>. </p><p> Examples: </p><pre class="screen"> 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) </pre></div><div class="sect2" id="id-1.11.7.24.7"><div class="titlepage"><div><div><h3 class="title">F.15.3. Metaphone</h3></div></div></div><p> Metaphone, like Soundex, is based on the idea of constructing a representative code for an input string. Two strings are then deemed similar if they have the same codes. </p><p> This function calculates the metaphone code of an input string: </p><a id="id-1.11.7.24.7.4" class="indexterm"></a><pre class="synopsis"> metaphone(text source, int max_output_length) returns text </pre><p> <code class="literal">source</code> has to be a non-null string with a maximum of 255 characters. <code class="literal">max_output_length</code> sets the maximum length of the output metaphone code; if longer, the output is truncated to this length. </p><p> Example: </p><pre class="screen"> test=# SELECT metaphone('GUMBO', 4); metaphone ----------- KM (1 row) </pre></div><div class="sect2" id="id-1.11.7.24.8"><div class="titlepage"><div><div><h3 class="title">F.15.4. Double Metaphone</h3></div></div></div><p> The Double Metaphone system computes two <span class="quote">“<span class="quote">sounds like</span>”</span> strings for a given input string — a <span class="quote">“<span class="quote">primary</span>”</span> and an <span class="quote">“<span class="quote">alternate</span>”</span>. In most cases they are the same, but for non-English names especially they can be a bit different, depending on pronunciation. These functions compute the primary and alternate codes: </p><a id="id-1.11.7.24.8.3" class="indexterm"></a><a id="id-1.11.7.24.8.4" class="indexterm"></a><pre class="synopsis"> dmetaphone(text source) returns text dmetaphone_alt(text source) returns text </pre><p> There is no length limit on the input strings. </p><p> Example: </p><pre class="screen"> test=# SELECT dmetaphone('gumbo'); dmetaphone ------------ KMP (1 row) </pre></div></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="file-fdw.html" title="F.14. file_fdw">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="hstore.html" title="F.16. hstore">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.14. file_fdw </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 11.11 Documentation">Home</a></td><td width="40%" align="right" valign="top"> F.16. hstore</td></tr></table></div></body></html>