title: SQLite Order By places umlauts & speical chars at end
body: <p>I'm using Phonegap to do a dictionary app for iOS.</p>
  
  <p>When querying the database for an alphabetical list I use <code>COLLATE NOCASE</code>:</p>
  
  <pre><code>ORDER BY term COLLATE NOCASE ASC
  </code></pre>
  
  <p>This solved the problem that terms starting with a lower case letter where appended to the end (Picked it up from <a href="https://stackoverflow.com/questions/2413427/how-to-use-sql-order-by-statement-to-sort-results-case-insensitive">that question</a>).</p>
  
  <p>However non-standard characters as öäüéêè  still get sorted in the end ~ here 2 examples:</p>
  
  <pre><code>    Expected: Öffnungszeiten     Oberved: Zuzahlung
                Zuzahlung                   Öffnungszeiten
  
  (or)          clé                         cliquer sur
                cliquer sur                 clé
  </code></pre>
  
  <p>I looked around and found similar matters discussed <a href="https://stackoverflow.com/questions/7813069/sqlite-3-character-issue-while-ordering-by-records">here</a> or <a href="https://stackoverflow.com/questions/3317672/how-to-order-sqlite-results-containing-umlauts-and-other-special-characters">here</a> but it seems the general advice is to install some type of extension </p>
  
  <blockquote>
    <p>This extension can probably help you ...</p>
    
    <p>...use ICU either as an extension</p>
    
    <p>SQLite supports integration with ICU ...</p>
  </blockquote>
  
  <p>But I'm not sure if this is applicable in my situation where the database is not hosted by myself but running on the customers device. So I'd guess I'd to ship this extension w/ my app-package.<br>
  I'm not very familiar with iOS but I've got the feeling that would be complicated - at least.</p>
  
  <p>Also in <a href="https://discussions.apple.com/thread/1700571?start=0&amp;tstart=0" rel="nofollow noreferrer">the official forum</a> I've found that hint:</p>
  
  <pre><code>SQLite does not properly handle accented characters.
  </code></pre>
  
  <p>and a little bit down in the text the poster mentions a bug in SQLite. </p>
  
  <p>All the links I've found haven't been active for >= 1 year and non of them seems to deal with the mobile environment I'm currently developing in.<br>
  So I was wondering if anyone else found a solution on their iOS projects.</p>
  
  <p>The documentation states they're only 3 default COLLATION option:</p>
  
  <blockquote>
    <p>6.0 Collating Sequences</p>
    
    <p>When SQLite compares two strings, it uses a collating sequence or
    collating function (two words for the same thing) to determine which
    string is greater or if the two strings are equal. SQLite has three
    built-in collating functions: BINARY, NOCASE, and RTRIM.</p>
  
  <pre><code>BINARY - Compares string data using memcmp(), regardless of text encoding.
  NOCASE - The same as binary, except the 26 upper case characters of ASCII are folded to their lower case equivalents before the
  </code></pre>
    
    <p>comparison is performed. Note that only ASCII characters are case
    folded. SQLite does not attempt to do full UTF case folding due to the
    size of the tables required.
        RTRIM - The same as binary, except that trailing space characters are ignored.</p>
  </blockquote>
  
  <p>For now my best guess would be to do the sorting in JavaScript but I suspect that this wouldn't do anything well to overall performance. </p>