title: How to prioritize a LIKE query select based on string position in field?
body: <p>I am attempting to query a table for a limited resultset in order to populate an autocomplete field in javascript. I am, therefore, using a LIKE operator with the partial string entered.</p>
  
  <p>If I have, for example, a table such as:</p>
  
  <pre><code>tblPlaces
  id     country
  1      Balanca
  2      Cameroon
  3      Canada
  4      Cape Verde
  5      Denmark
  </code></pre>
  
  <p>For the sake of this example, let's say I want two rows returning - and yeah, for this example, I made up a country there ;) I want to prioritize any instance where a partial string is matched at the beginning of country. The query I began using, therefore is:</p>
  
  <pre><code>SELECT id, country FROM tblPlaces WHERE country LIKE 'ca%' LIMIT 2
  </code></pre>
  
  <p>This returned 'Cameroon' and 'Canada' as expected. However, in instances where there are no two names in which the string is matched at the beginning of a word (such as 'de'), I want it to look elsewhere in the word. So I revised the query to become</p>
  
  <pre><code>SELECT id, country FROM tblPlaces WHERE country LIKE '%ca%' LIMIT 2
  </code></pre>
  
  <p>This then returned 'Cape Verde' and 'Denmark', but in doing so broke my original search for 'ca', which now returns 'Balanca' and 'Cameroon'.</p>
  
  <p>So, my question is, how to go about this using a single query that will prioritize a match at the start of a word (perhaps I need to use REGEXP?) I am assuming also that if the 'country' column is indexed, these matches will at least be returned with subsequent alphabetical priority (i.e. Cameroon before Canada etc).</p>