title: H2 database collation: what to choose?
body: <p>After a lot of reading and experimentation, it seems like I want PRIMARY strength for searching, but TERTIARY or IDENTICAL for ordering. <strong>Main question: Is that possible to achieve with H2 (or any other DB)?</strong></p>
  
  <p>Secondary question: Am I the only one here or would any of you also like the above combination? Some confirmation would be helpful for my sanity.</p>
  
  <p>Background:
  It seems like the collation can only be set at the very beginning when creating the database. So I want to make sure to pick the right one. I am mainly thinking of these use cases (for now):</p>
  
  <ol>
  <li><p>A search field where the user can start typing to filter a table: Here PRIMARY seems the most appropriate, in order to avoid missing any results (user is used to Google...). Although, it would be nice to be able to give the user the option to enable secondary or tertiary collation to do more precise searching.</p></li>
  <li><p>Ordering: When the user clicks a table column to order the contents, TERTIARY/IDENTICAL ordering seems appropriate. That's what I am used to from everyday experience.</p></li>
  </ol>
  
  <p>I read the offical H2 docs here: <a href="http://www.h2database.com/html/commands.html#set_collation" rel="nofollow noreferrer">http://www.h2database.com/html/commands.html#set_collation</a>.
  and here: <a href="http://www.h2database.com/html/datatypes.html#varchar_ignorecase_type" rel="nofollow noreferrer">http://www.h2database.com/html/datatypes.html#varchar_ignorecase_type</a>
  Some more related info:
  <a href="https://stackoverflow.com/questions/3739989/collation-strength-and-local-language-relation">Collation STRENGTH and local language relation</a></p>
  
  <p>The test sql (from <a href="https://groups.google.com/forum/?fromgroups=#!topic/h2-database/lBksrrcuGdY" rel="nofollow noreferrer">https://groups.google.com/forum/?fromgroups=#!topic/h2-database/lBksrrcuGdY</a>):</p>
  
  <pre><code>drop all objects;
  set collation english STRENGTH PRIMARY;
  create table test(name varchar);
  insert into test values ('À'), ('Ä'), ('Â'), ('A'), ('à'), ('ä'), ('â'), ('a'), ('àa'), ('äa'), ('âa'), ('aa'), ('B'), ('b');
  select * from test where name like 'a' order by name;
  select * from test order by name;
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/CGe4o.png" alt="enter image description here"></p>