title: Order by substring of ID as integer in HQL with Grails
body: <p>Is there a way to order by substring of ID casted to integer using HQL or/and Criteria?</p>
  
  <p>For example:</p>
  
  <pre><code>class Foo {
    String id
    String name
  }
  </code></pre>
  
  <p>Sorted Dataset would look like:</p>
  
  <pre><code>90-1  David
  90-4  Mike
  101-1 Jack
  101-2 Peter
  105-1 Jon
  105-7 Jane
  </code></pre>
  
  <p>With MySQL database and native sql it would be done like:</p>
  
  <pre><code>order by 
  CAST(substring_index(id, '-', 1) AS UNSIGNED),
  CAST(substring(id, length(substring_index(id, '-', 1)) + 2) AS UNSIGNED)
  </code></pre>
  
  <p>I tried using formula as suggested but that did not work because UNSIGNED was treated as alias and generated SQL had _this.UNSIGNED</p>
  
  <pre><code>Integer fooFirstID
  Integer fooSecondID
  
  static mapping = {
      fooFirstID formula: "CAST(substring_index(id, '-', 1) AS UNSIGNED)"
      fooSecondID formula: "CAST(substring(id, length(substring_index(id, '-', 1))
                             + 2) AS UNSIGNED)" 
  }
  </code></pre>
  
  <p>I see that Hibernate supports substring as well as cast to Hibernate type but cannot find equivalent of a substring_index.</p>
  
  <p>Thanks.</p>