title: SQLite LIKE & ORDER BY Match query
body: <p>I need a SQLite query that searches 1 field only using LIKE.</p>
  
  <p>Basic example: </p>
  
  <pre><code>SELECT name FROM table WHERE name LIKE "%John%" ORDER BY name LIMIT 10;
  </code></pre>
  
  <p>The problem is that I want the result to be ordered in this way:</p>
  
  <ol>
  <li>If the field is equal (e.g. "John")</li>
  <li>If the field starts with "John" (e.g. "John Doe")</li>
  <li>If the field contains "John" (e.g. "Jane John Doe")</li>
  </ol>
  
  <p>The following query achieves the expected result, but is slow:</p>
  
  <pre><code>SELECT name FROM table WHERE name LIKE "%John%" ORDER BY CASE WHEN name = "John" 
  THEN 1 ELSE 2 END, CASE WHEN name LIKE "John%" THEN 1 ELSE 2 END, name LIMIT 10;
  </code></pre>
  
  <p>The query above is slower (or I tested it incorrectly) than the alternative of using 3 separate queries (one for exact match, one for starts with and one for contains).</p>
  
  <p>Are there any other alternatives?</p>