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


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

""Álvaro G. Vicario"" <alvaroNOSPAMTHANKS (AT) demogracia (DOT) com> schreef in bericht
news:gc4ivc$1ne$1 (AT) huron (DOT) algomas.org...
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

""Álvaro G. Vicario"" <alvaroNOSPAMTHANKS (AT) demogracia (DOT) com> schreef in bericht
news:gc4ivc$1ne$1 (AT) huron (DOT) algomas.org...
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

""Álvaro G. Vicario"" <alvaroNOSPAMTHANKS (AT) demogracia (DOT) com> schreef in bericht
news:gc4ivc$1ne$1 (AT) huron (DOT) algomas.org...
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

""Álvaro G. Vicario"" <alvaroNOSPAMTHANKS (AT) demogracia (DOT) com> schreef in bericht
news:gc4ivc$1ne$1 (AT) huron (DOT) algomas.org...
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

On Oct 3, 9:51*am, "Álvaro G. Vicario"
<alvaroNOSPAMTHA... (AT) demogracia (DOT) com> wrote:
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

On Oct 3, 9:51*am, "Álvaro G. Vicario"
<alvaroNOSPAMTHA... (AT) demogracia (DOT) com> wrote:
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

On Oct 3, 9:51*am, "Álvaro G. Vicario"
<alvaroNOSPAMTHA... (AT) demogracia (DOT) com> wrote:
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

On Oct 3, 9:51*am, "Álvaro G. Vicario"
<alvaroNOSPAMTHA... (AT) demogracia (DOT) com> wrote:
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

On Oct 3, 8:34*am, F Pighi <francesco.pi... (AT) gmail (DOT) com> wrote:
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 --