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

Answer 2

In PHP, the natsort function will sort an array of strings the way a human would, instead of in ASCII order.

Answer 3

Jeff also has a post on the topic, with more resources for other languages.

Answer 4

A related question - which sort algorithm works best on mostly sorted data?

Edit

This is now a separate question: Which sort algorithm works best on mostly sorted data

Answer 5

http://stackoverflow.com/questions/220044/which-sort-algorithm-works-best-on-mostly-sorted-data

Answer 6

I keep seeing "solutions" to this problem that involve trying to find the first numbers in the string. But sometimes you have strings like "123_456_7" vs. "123_456_10" vs. "123_8_10", and a "natural sort order" would do:

123_8_10 123_456_7 123_456_10

while alphabetical sort would do 123_8_10 123_456_7 123_456_10

What we really need is for Oracle to implement natural sort order into their product so that it is available inherently. This would also allow indexes to be built on the natural sort order. As it is, we all have to do tricks (0-pad; "to_number" and substring, etc.).

Answer 7

According to a colleague of mine you can achieve natural sorting in Oracle and MySQL like this:

ORDER BY fieldName + 0 ASC

I haven't tried it myself, but I guess it's worth a shot.