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

Question

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.

Answer 1

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);