title: SQL Server SELECT query ordering by substring
body: <p>I have a column in a SQL Server table that has the following rows:</p>
  
  <p>MyColumn : <code>C1_xxx1,C2_xxx1,C3_xxx1,C1_xxx2,C1_xxx3,C3_xxx2</code>  etc</p>
  
  <p>It is a <code>text</code> column that contains strings that have the following format: <code>CY_mystring</code> where Y is a number from 1 to 5, followed by the '_' character then <code>mystring</code> that can have any value.</p>
  
  <p>Is there a way to make a select return this column ordered as following:</p>
  
  <pre><code>C1_xxx1
  C1_xxx2
  C1_xxx3
  ......
  C1_xxxn
  C2_xxx1
  ......
  C2_xxxn
  C3_xxx1
  .......
  C3_xxxn
  </code></pre>
  
  <p>etc</p>
  
  <p>Ordered by the <code>CY_</code> substring.</p>
  
  <p>thank you</p>