Re: Natural sort
Date: Fri, 3 Oct 2008 06:34:50 -0700 (PDT)
Message-ID: <2d1a7ef4-2bf9-44e5-8522-107d2e1329bc@v53g2000hsa.googlegroups.com>
On Oct 3, 8:34 am, F Pighi <francesco.pi..._at_gmail.com> wrote:
> On Oct 3, 9:51 am, "Álvaro G. Vicario"
>
> <alvaroNOSPAMTHA..._at_demogracia.com> wrote:
> > Does Oracle 10g have a so called "natural sort" feature? E.g.:
>
> > foo1
> > foo2
> > foo10
> > foo100
>
> > ... rather than:
>
> > foo1
> > foo10
> > foo100
> > foo2
>
> You could do something like this:
>
> select * from YOUR_TABLE
> order by to_number(regexp_substr(YOUR_FIELD,'^[0-9]+')),
> to_number(regexp_substr(YOUR_FIELD,'$[0-9]+')),
> YOUR_FIELD
>
> --
> Francesco Pighi
Francesco's solution requires version 10g+. Also if foo is not a constant for all rows then this prefix would need to be separated from the following character digits to be used as the first value in the sort.
For earlier versions you can use various Oracle functions such as substr, transform, to_number, etc ... to convert the single column into 2 separate items: the string and a number and then sort on the combination of columns. It is real easy if the prefix characters are fixed length but still possible if the character portion is variable length.
HTH -- Mark D Powell -- Received on Fri Oct 03 2008 - 08:34:50 CDT