title: How to search between a combination of varchar and numeric values in SQL?
body: <p>So, I know this is bad database design but unfortunately this is what I have to use for my job, and I'm struggling to figure it out.</p>
  
  <p>I'm writing a sql query that checks if the value of a certain field is between two values.</p>
  
  <pre><code>Select * 
  from TestTable 
  where TestField between ValueA and ValueB
  </code></pre>
  
  <p>The only problem is that ValueA and ValueB and TestField can be either integers or varchars. They can either be something like 100 or something like X100. Values that start with X are greater than any integer without an X.</p>
  
  <p>So, if ValueA is 100 and ValueB is 2000, I want to return all rows where Testfield is between 100 and 2000. </p>
  
  <p>However, if ValueA is 100 and ValueB is X500, I want to return all integers greater than 100 and all values that start with X and the trailing number less than 500.</p>
  
  <p>For example the order would be, from smallest to greatest:</p>
  
  <pre><code>1. 100
  2. 500
  3. 1000
  4. 5000
  5. X100
  6. X500
  7. X1000
  8. X2000
  </code></pre>
  
  <p>So, for my question: <strong>How could I write a query that accurately searches between ValueA and ValueB?</strong> </p>
  
  <p>With valueA and valueB as varchars it prevents me from accurately checking which numbers are greater. </p>