Natural sort

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Álvaro G. Vicario - 03 Oct 2008 08:51 GMT

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

Signature

-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://bits.demogracia.com
-- Mi web de humor al baño María: http://www.demogracia.com
--

Reply to this Message

Shakespeare - 03 Oct 2008 09:57 GMT

> Does Oracle 10g have a so called "natural sort" feature? E.g.:
>
[quoted text clipped - 13 lines]
> 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...

I don't think so. You could write a pl/sql function, returning
foo001
foo002
foo010
foo100

and sort on this function (you could even create a function based index on
it)

Shakespeare

Reply to this Message

F Pighi - 03 Oct 2008 13:34 GMT

On Oct 3, 9:51 am, "Álvaro G. Vicario"
<alvaroNOSPAMTHA...@demogracia.com> wrote:

> Does Oracle 10g have a so called "natural sort" feature? E.g.:
>
[quoted text clipped - 9 lines]
> 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

Reply to this Message

Mark D Powell - 03 Oct 2008 14:34 GMT

> On Oct 3, 9:51 am, "Álvaro G. Vicario"
>
[quoted text clipped - 22 lines]
> --
> 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 --

Reply to this Message

Álvaro G. Vicario - 06 Oct 2008 17:11 GMT

F Pighi escribió:

>> Does Oracle 10g have a so called "natural sort" feature? E.g.:
>>
[quoted text clipped - 16 lines]
> to_number(regexp_substr(YOUR_FIELD,'$[0-9]+')),
> YOUR_FIELD

I suppose the second regexp is '[0-9]+$'. Perhaps it's not a 100%
generic solution but it's simple enough to be usable and it works pretty
well with the actual data currently stored in the table. Thank you!

P.S. I have Oracle 10g release 10.1.0.2.0 so I can use REGEXP_SUBSTR;
sorry for omitting the release.

Signature

-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://bits.demogracia.com
-- Mi web de humor al baño María: http://www.demogracia.com
--

Reply to this Message

DA Morgan - 03 Oct 2008 21:14 GMT

> Does Oracle 10g have a so called "natural sort" feature? E.g.:
>
[quoted text clipped - 13 lines]
> 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>

Signature

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

Reply to this Message

Shakespeare - 06 Oct 2008 08:03 GMT

>> Does Oracle 10g have a so called "natural sort" feature? E.g.:
>>
[quoted text clipped - 61 lines]
>
> SQL>

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

Reply to this Message

DA Morgan - 07 Oct 2008 14:29 GMT

>>> Does Oracle 10g have a so called "natural sort" feature? E.g.:
>>>
[quoted text clipped - 76 lines]
>
> 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.

Signature

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

Reply to this Message

mh@pixar.com - 21 Oct 2008 00:25 GMT

> But, quite frankly, if what you are
> asking is the case then the data should be stored in two separate
> columns.

There might be at least one case where this is useful... filenames.

Right now I cavalierly present some lists of filenames from a
table in "non-natural" order, but I'm going to look into switching
to natural order with this trick.

Signature

Mark Harrison
Pixar Animation Studios

Reply to this Message