title: How to get the max value from a column with alphanumeric strings?
body: <p>I have a table which contains invoice and estimate numbers.The invoice numbers are like <code>"IN1000","IN1001","IN1002"</code> and so on.The estimate numbers are like <code>"ES101","ES102","ES103"</code>.How can I get the max of both my invoice and estimate? I would also like it to be cast into an integer like 1000.</p>
  
  <p>I have tried the following query:</p>
  
  <pre><code>SELECT Max(CAST (SUBSTR(invoiceNo,3) AS UNSIGNED)) FROM selected_items 
  WHERE invoiceNo RLIKE 'IN';
  </code></pre>
  
  <p>When I run this query I get the following error :</p>
  
  <blockquote>
    <p>#1064 - You have an error in your SQL syntax; check the manual that
    corresponds to your MySQL server version for the right syntax to use
    near 'UNSIGNED)) FROM selected_items WHERE invoiceNo RLIKE 'IN'' at
    line 1.</p>
  </blockquote>
  
  <p>And I am using similar approach for estimate :</p>
  
  <pre><code>SELECT Max(CAST (SUBSTR(invoiceNo,3) AS UNSIGNED)) FROM selected_items
  WHERE invoiceNo RLIKE 'ES';
  </code></pre>
  
  <p>How can I do both the operations in one query? Any help is appreciated.Thank you.</p>