title: SQL Server sort order with nonprintable characters
body: <p>I have a scalar value function that returns a varchar of data containing the ASCII unit seperator Char(31).  I am using this result as part of an Order By clause and attempting to sort in ascending order.  </p>
  
  <p>My scalar value function returns results like the following (nonprintable character spelled out for reference)</p>
  
  <ul>
  <li>ABC</li>
  <li>ABC (CHAR(31)) DEF</li>
  <li>ABC (CHAR(31)) DEF (CHAR(31)) HIJ</li>
  </ul>
  
  <p>I would expect that when I order by ascending the results would be the following:</p>
  
  <ul>
  <li>ABC</li>
  <li>ABCDEF</li>
  <li>ABCDEFHIJ</li>
  </ul>
  
  <p>instead I am seeing the results as the complete opposite:</p>
  
  <ul>
  <li>ABCDEFHIJ</li>
  <li>ABCDEF</li>
  <li>ABC</li>
  </ul>
  
  <p>Now I am fairly certain that this has to do with the non-printable characters, but I am not sure why.  Any idea as to why that is the case?</p>
  
  <p>Thanks</p>