title: Why is this MySQL query not ordering my row values naturally?
body: <p>I have the following query:</p>
  
  <pre><code>SELECT 
      series, scott
  FROM
      stampitemdetails
  WHERE
      scott REGEXP '^C[0-9]'
  ORDER BY SUBSTR(scott, 1, 1) , CAST(SUBSTR(scott, 2, 100) AS UNSIGNED)
  </code></pre>
  
  <p>It returns my rows almost in the correct order, but when there are letters after numbers, they are <strong>sometimes</strong> out of order. Others appear ordered correctly. Here is what returns:</p>
  
  <pre><code>series          scott
  Airmail Stamps  C1
  Airmail Stamps  C2
  Airmail Stamps  C3
  Airmail Stamps  C3a
  Airmail Stamps  C4
  Airmail Stamps  C5
  Airmail Stamps  C6
  Airmail Stamps  C7
  Airmail Stamps  C8
  Airmail Stamps  C9
  Airmail Stamps  C10b
  Airmail Stamps  C10a
  Airmail Stamps  C10
  Airmail Stamps  C11
  Airmail Stamps  C11a
  Airmail Stamps  C12
  Airmail Stamps  C12a
  Airmail Stamps  C13
  Airmail Stamps  C14
  Airmail Stamps  C15
  Airmail Stamps  C16
  Airmail Stamps  C17
  Airmail Stamps  C18
  Airmail Stamps  C19
  Airmail Stamps  C20
  Airmail Stamps  C21
  Airmail Stamps  C22
  Airmail Stamps  C23
  Airmail Stamps  C23a
  Airmail Stamps  C23b
  Airmail Stamps  C23c
  Airmail Stamps  C24
  Airmail Stamps  C25
  Airmail Stamps  C25a
  Airmail Stamps  C25b
  Airmail Stamps  C26a
  Airmail Stamps  C26
  Airmail Stamps  C27
  Airmail Stamps  C28
  Airmail Stamps  C29
  Airmail Stamps  C30
  Airmail Stamps  C31
  Airmail Stamps  C32
  Airmail Stamps  C33
  Airmail Stamps  C34
  Airmail Stamps  C34a
  Airmail Stamps  C35
  Airmail Stamps  C35a
  Airmail Stamps  C35b
  Airmail Stamps  C36
  Airmail Stamps  C36a
  Airmail Stamps  C37
  Airmail Stamps  C38
  Airmail Stamps  C39
  Airmail Stamps  C39a
  Airmail Stamps  C39b
  Airmail Stamps  C39c
  Airmail Stamps  C40
  Airmail Stamps  C41
  Airmail Stamps  C42
  Airmail Stamps  C43
  Airmail Stamps  C44
  Airmail Stamps  C45
  Airmail Stamps  C46
  Airmail Stamps  C47
  Airmail Stamps  C48
  Airmail Stamps  C49
  Airmail Stamps  C50
  Airmail Stamps  C51b
  Airmail Stamps  C51
  Airmail Stamps  C51a
  </code></pre>
  
  <p>If you look at C10, it should be in the following order instead:</p>
  
  <pre><code>C10
  C10a
  C10b
  </code></pre>
  
  <p>And C51 should be:</p>
  
  <pre><code>C51
  C51a
  C51b
  </code></pre>
  
  <p>Can someone explain what is wrong and how to fix this query? Thank you!</p>