Re: Natural sort

Álvaro G. Vicario wrote:
> Does Oracle 10g have a so called "natural sort" feature? E.g.:
>
> foo1
> foo2
> foo10
> foo100
>
> ... rather than:
>
> foo1
> foo10
> foo100
> foo2
>
> Docs says I can use NLSSORT() in ORDER BY clauses but available values
> for NLS_SORT do not seem to cover this case. I Google for "natural sort
> in oracle" and all I get is how to do it in C++ and PHP...

In any version:

SQL> create table fubar (

   2 testcol VARCHAR2(10));

Table created.

SQL> insert into fubar values ('foo10');

1 row created.

SQL> ed
Wrote file afiedt.buf

   1* insert into fubar values ('foo2')
SQL> / 1 row created.

SQL> ed
Wrote file afiedt.buf

   1* insert into fubar values ('foo100') SQL> / 1 row created.

SQL> ed
Wrote file afiedt.buf

   1* insert into fubar values ('foo1')
SQL> / 1 row created.

SQL> SELECT *
   2 FROM fubar
   3 ORDER BY TO_NUMBER(SUBSTR(testcol, 4));

TESTCOL

SQL>

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org