Using Generic Comparator in Oracle ORDER BY

I am working with a database column containing heirarichal entries with values of the form

<string>-<number>(.<number>)*

e.g.: A-4.1, A-4.1.1, A-4.1.2,..., A-4.1.10, A-4.1.10.1, A-4.2, B-1.1, B-1.2, etc.

The depth of the heirarchy is unlimitted (A-4.1.1.1.1.1.1.1 would be okay), and the width is unlimitted as well (B-2.99999 would be okay).

How can I ORDER BY these fields using the order implied in the opening paragraph? In most programming paradigms, I would write a custom comparator:
f(v1,v2) returning {-1 if v1<v2, 0 otherwise} and a printing iterator that uses that comparator.

But even if I wrote this comparator function in PL/SQL, how could I incorporate it into an SQL ORDER BY clause (as ORDER BY doesn't allow for generic comparators)?

Currently, the only alternative I can think of is a mess of SUBSTR/INSTR to some arbitrary depth, which would be cumbersome and would ultimately fail with the first entry beyond that depth.

Consider:
[code]
SELECT c1 FROM my_table
ORDER BY
-- depth 1

TO_NUMBER(SUBSTR(c1,

                 INSTR(c1,'-',1,1)+1,

DECODE(INSTR(c1,'.',1,1),0,LENGTH(c1)+1,INSTR(c1,'.',1,1))-DECODE(INSTR(c1,'-',1,1),0,LENGTH(c1)+1,INSTR(c1,'-',1,1))
-1)) ,

• depth 2 TO_NUMBER(SUBSTR(br, INSTR(BR,'.',1,1)+1,

...
[/code]

yech!

Am I overlooking any handy tools from Oracle's repetoire (I'm on a 9i database)?

Thanks! Received on Wed Oct 26 2005 - 10:58:45 CDT