Oracle: How can I implement a "natural" order-by in a SQL query?

oracle, sql

Solution

You can use functions in your order-by clause. In this case, you can split the non-numeric and numeric portions of the field and use them as two of the ordering criteria.

select * from t
 order by to_number(regexp_substr(a,'^[0-9]+')),
          to_number(regexp_substr(a,'[0-9]+$')),
          a;

You can also create a function-based index to support this:

create index t_ix1
    on t (to_number(regexp_substr(a, '^[0-9]+')),
          to_number(regexp_substr(a, '[0-9]+$')), 
          a);

Problem

e.g, ``` foo1 foo2 foo10 foo100 ``` rather than ``` foo1 foo10 foo100 foo2 ``` Update: not interested in coding the sort myself (although that's interesting in its own right), but having the database to do the sort for me.

Original source

Related problems