title: utf-8 string similarity in postgresql 10.1
body: <p>:) I'm trying to find strings similarity in PostgreSQL 10.1.</p>
  
  <p>I use the extensions <code>unaccent</code> and <code>pg_trgm</code> and enabled them with:</p>
  
  <pre><code>create extension unaccent;
  create extension pg_trgm;
  </code></pre>
  
  <p>The problem is that <code>pg_trgm</code> does not provide utf8 support. So if I execute:</p>
  
  <pre><code>select similarity('כפיר','כפיר');
  </code></pre>
  
  <p>it returns similarity of zero.</p>
  
  <p>I decided to convert that string to an <code>iso-8859-8</code> encoding which supports Hebrew and English, the languages that I use in this case.</p>
  
  <p>But first I want to <code>unaccent</code> the string, so if I have <code>כפיר - ƒ</code> it will first be converted to <code>כפיר - f</code> so it will be properly converted to the desired character encoding.</p>
  
  <p>So <code>select unaccent('כפיר - ƒ');</code> does return the proper results.</p>
  
  <p>So unfortunately executing:</p>
  
  <pre><code>select convert(unaccent('כפיר - ƒ'),'UTF8','ISO_8859_8');
  </code></pre>
  
  <p>returns</p>
  
  <blockquote>
    <p>[42883] ERROR: function convert(text, unknown, unknown) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 8</p>
  </blockquote>
  
  <p>When I check the documentation at <a href="https://www.postgresql.org/docs/9.1/functions-string.html" rel="nofollow noreferrer">https://www.postgresql.org/docs/9.1/functions-string.html</a> regarding <code>convert()</code> function it requires a string.</p>
  
  <p>If I try to cast something as string, I get that <code>type "string" does not exist</code></p>
  
  <p>OK. So working with unaccent is the first issue that I need to resolve (and maybe the only issue). What I did next returned proper results without me seeing the proper string in hebrew.</p>
  
  <p>What I mean is this..</p>
  
  <p>Executing <code>select convert('כפיר','UTF-8','ISO_8859_8');</code> returns</p>
  
  <pre><code>4 B 00000000  EB F4 E9 F8                                        ëôéø
  </code></pre>
  
  <p>and casting it to text with <code>select convert('כפיר','UTF8','ISO_8859_8')::text;</code> returns <code>\xebf4e9f8</code> </p>
  
  <p>I'm guessing these are the write characters and because of the change in encoding I don't see the actual Hebrew characters. am I wrong to assume that ? that's the 2nd issue.</p>
  
  <p>So if I don't use accent and execute: </p>
  
  <pre><code>select similarity(convert('כפייר עזר','UTF8','ISO_8859_8')::text, convert('כפיר','UTF8','ISO_8859_8')::text);
  </code></pre>
  
  <p>it returns a similarity of 0.5 which is OK.</p>
  
  <p>To sum up, my questions are:</p>
  
  <ol>
  <li>How to cast <code>unaccent</code> properly to be able to use it in <code>convert</code></li>
  <li>Am I casting my utf-8 Hebrew string properly to ASCII?</li>
  <li>Am I trying to resolve the issue the right way - is there a different extension for similarity function that supports utf-8? </li>
  <li>I also want to remove any character that is not alphanumeric from the string before I do execute the similarity function in order to get better similarity results based on my needs. I think about using regular expressions about converting the string to ASCII. something like: <code>regexp_replace('string', '\W+', '', 'g')</code>. Is that the way to go? Is there a regexp_replace that supports utf8 ?</li>
  </ol>
  
  <p>Thank You!</p>