title: How can I prevent SQLite from treating a string as a number?
body: <p>I would like to query an SQLite table that contains directory paths to find all the paths under some hierarchy. Here's an example of the contents of the column:</p>
  
  <pre><code>/alpha/papa/
  /alpha/papa/tango/
  /alpha/quebec/
  /bravo/papa/
  /bravo/papa/uniform/
  /charlie/quebec/tango/
  </code></pre>
  
  <p>If I search for everything under <code>/bravo/papa/</code>, I would like to get:</p>
  
  <pre><code>/bravo/papa/
  /bravo/papa/uniform/
  </code></pre>
  
  <p>I am currently trying to do this like so (see below for the long story of why I can't use more simple methods):</p>
  
  <pre><code>SELECT * FROM Files WHERE Path &gt;= '/bravo/papa/' AND Path &lt; '/bravo/papa0';
  </code></pre>
  
  <p>This works. It looks a bit weird, but it works for this example. '0' is the unicode code point  1 greater than '/'. When ordered lexicographically, all the paths starting with '/bravo/papa/' compare greater than it and less than 'bravo/papa0'. However, in my tests, I find that this breaks down when we try this:</p>
  
  <pre><code>SELECT * FROM Files WHERE Path &gt;= '/' AND Path &lt; '0';
  </code></pre>
  
  <p>This returns no results, but it should return every row. As far as I can tell, the problem is that SQLite is treating '0' as a number, not a string. If I use '0Z' instead of '0', for example, I do get results, but I introduce a risk of getting false positives. (For example, if there actually was an entry '0'.)</p>
  
  <p>The simple version of my question is: is there some way to get SQLite to treat '0' in such a query as the length-1 string containing the unicode character '0' (which should sort strings such as '!', '*' and '/', but before '1', '=' and 'A') instead of the integer 0 (which SQLite sorts before all strings)?</p>
  
  <p>I think in this case I can actually get away with special-casing a search for everything under '/', since all my entries will always start with '/', but I'd really like to know how to avoid this sort of thing in general, as it's unpleasantly surprising in all the same ways as Javascript's "==" operator.</p>
  
  <h2>First approach</h2>
  
  <p>A more natural approach would be to use the LIKE or GLOB operator. For example:</p>
  
  <pre><code>SELECT * FROM Files WHERE Path LIKE @prefix || '%';
  </code></pre>
  
  <p>But I want to support all valid path characters, so I would need to use ESCAPE for the '_' and '%' symbols. Apparently this prevents SQLite from using an index on Path. (See <a href="http://www.sqlite.org/optoverview.html#like_opt" rel="noreferrer">http://www.sqlite.org/optoverview.html#like_opt</a> ) I really want to be able to benefit from an index here, and it sounds like that's impossible using either LIKE or GLOB unless I can guarantee that none of their special characters will occur in the directory name, and POSIX allows anything other than NUL and '/', even GLOB's '*' and '?' characters.</p>
  
  <p>I'm providing this for context. I'm interested in other approaches to solve the underlying problem, but I'd prefer to accept an answer that directly addresses the ambiguity of strings-that-look-like-numbers in SQLite.</p>
  
  <h2>Similar questions</h2>
  
  <p><a href="https://stackoverflow.com/questions/12377992/how-do-i-prevent-sqlite-from-evaluating-a-string-as-a-math-expression">How do I prevent sqlite from evaluating a string as a math expression?</a></p>
  
  <p>In that question, the values weren't quoted. I get these results even when the values are quoted or passed in as parameters.</p>
  
  <hr>
  
  <p><em>EDIT</em> - See my answer below. The column was created with the invalid type "STRING", which SQLite treated as NUMERIC.</p>