<p>e.g,</p>

<pre><code>foo1
foo2
foo10
foo100
</code></pre>

<p>rather than</p>

<pre><code>foo1
foo10
foo100
foo2
</code></pre>

<p>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.</p>

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