Custom sort in Drupal Views

I have a View that uses fields from the Biblio module and those are unfortunately all defined as text fields. I need to sort my view according to the accession number, but if define the sorting in the View it sorts the numbers wrong like

1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 2, 20 ...

instead of

1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 ...

I assume this is becaus开发者_开发问答e Views interprets those as string and sorts them accordingly.

Is there an easy way to create a custom sort function that would sort those as numbers? Or maybe I could override the autogenerated SQL (I know how to do that), but I don't know how to sort strings as numbers in SQL.

You can use CAST or CONVERT to make the text into numbers and just do normal sorting, documentation

Consider using the computed field module ( http://drupal.org/project/computed_field ). It allows you to set the value of the field as a computed value of other CCK fields in the content type.

Make a computed field. Set the data type to be an Integer. In the settings for computed field let

$node_field[0]['value'] = (int)($node->field_biblio_that_has_text[0]['value']);

Now you can sort on the basis of the computed field and sorting will happen numerically and not textually. Of course you need to change field_biblio_that_has_text to the machine name of the biblio field (go to manage fields and you will find the name there).

To learn more about computed field (and learn when exactly computed field is calculated) check out http://drupal.org/node/126522

You can use this module https://drupal.org/project/views_natural_sort it has the option to sort by different ways