Re: Natural sort

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 07 Oct 2008 06:29:55 -0700
Message-ID: <1223386193.477492@bubbleator.drizzle.com>

Shakespeare wrote:

> "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

Easily modified if you substring out both the leading alphas and the trailing numerics. But, quite frankly, if what you are asking is the case then the data should be stored in two separate columns.

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

Received on Tue Oct 07 2008 - 08:29:55 CDT