title: Using char index to find numeric values
body: <p>I have a column on a mysql table that stores mostly numeric values, but sometimes strings. It's defined as VARCHAR(20). There is an index on this column for the first four characters.</p>
  
  <pre><code>ADD INDEX `refNumber` USING BTREE(`refNumber`(4));
  </code></pre>
  
  <p>Since the field is mostly numeric, it is useful for the user to be able to query for values that fall within an numeric range (e.g., 100 to 2000). If I use a numeric comparison, this index is not used. </p>
  
  <pre><code>WHERE refNumber BETWEEN 100 AND 2000
  </code></pre>
  
  <p>If I use a string comparison, I get some values I don't want (e.g., 10000 comes back when querying for a range of 100 to 2000). </p>
  
  <pre><code>WHERE refNumber BETWEEN '100' AND '2000'
  </code></pre>
  
  <p>Is there a good solution to this?</p>
  
  <p>Note: there are some values that are recorded with zeros padded on the front like 0234, which should be returned when looking for values between 100 and 2000.</p>