title: SELECT by string prefix using an index
body: <p>You have a column <code>foo</code>, of some string type, with a index on that column. You want to <code>SELECT</code> from the table <code>WHERE</code> the <code>foo</code> column has the prefix <code>'pre'</code>. Obviously, the index should be able to help here.</p>
  
  <p>Here is the most obvious way to search by prefix:</p>
  
  <pre><code>SELECT * FROM tab WHERE foo LIKE 'pre%';
  </code></pre>
  
  <p>Unfortunately, this does not get optimized to use the index (in Oracle or Postgres, at least).</p>
  
  <p>The following, however, <em>does</em> work:</p>
  
  <pre><code>SELECT * FROM tab WHERE 'pre' &lt;= foo AND foo &lt; 'prf';
  </code></pre>
  
  <p>But are there better ways to accomplish this, or are there ways of making the above more elegant? In particular:</p>
  
  <ul>
  <li>I need a function from <code>'pre'</code> to <code>'prf'</code>, but this has to work for any underlying collation. Also, it's more complicated than above, because if searching for e.g. <code>'prz'</code> then the upper bound would have to be <code>'psa'</code>, and so on.</li>
  <li>Can I abstract this into a stored function/procedure and still hit the index? So I could write something like <code>... WHERE prefix('pre', foo);</code>?</li>
  </ul>
  
  <p>Answers for all DBMSes appreciated.</p>