Re: Natural sort

"DA Morgan" <damorgan_at_psoug.org> schreef in bericht news:1223064876.331930_at_bubbleator.drizzle.com...
> Á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
> ----------
> foo1
> foo2
> foo10
> foo100
>
> 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

But this would sort
foo1
foo2
bar3
foo4

I admit, it's according to the specs, but I guess that's not the sort one wants.
So at least it should be
> 3 ORDER BY SUBSTR(testcol, 1,3), TO_NUMBER(SUBSTR(testcol, 4));

and I don't think the numbers will allways be at pos 4. That's why I proposed the function.

Shakespeare Received on Mon Oct 06 2008 - 02:03:15 CDT