Re: Sorting paragraph numbers (multiple decimal place values)

Earlier Mike Grapevine wrote (in very long lines):
> Hi,
> I am using Oracle to sort paragraph numbers, as specified with the "order by" clause
> in a select statment. This does not directly work, as say, when sorting the paragraph numbers
> 1.11.3.4, 2.3.5, and 11.8.3, as theses values will be put in the order of 1.11.3.4, 11.8.3, and
> then 2.3.5, obviously not the order of the paragraphs in the document. Someone mentioned a
> "collation sequence" to me to correctly order these type of values, but I haven't been able to
> find any information on it. Has anybody done any sorting before of values like these that
> contain multiple decimal places. I guess the trouble stems from trying to do an ASCII based
> sort on these values, instead of treating them as somewhat numerical values.
> --
> Michael D. Grapevine

         [.sig stuff truncated]

Hi,

Since I ran into a similar situation not too long ago I figured I would share with you the code that got me through it:

ORDER BY
LPAD(NVL(SUBSTR(dbfield,1,INSTR(dbfield,'.')-1),'0'),27,'0'), LPAD(NVL(SUBSTR(dbfield,INSTR(dbfield,'.',2,1)+1,   INSTR(dbfield,'.',1,2) - INSTR(dbfield,'.',1,1)-1),'0'),27,'0'), LPAD(NVL(SUBSTR(dbfield,INSTR(dbfield,'.',2,2)+1,   INSTR(dbfield,'.',1,3) - INSTR(dbfield,'.',1,2)-1),'0'),27,'0'), LPAD(NVL(SUBSTR(dbfield,INSTR(dbfield,'.',2,3)+1,   INSTR(dbfield,'.',1,4) - INSTR(dbfield,'.',1,3)-1),'0'),27,'0'), LPAD(NVL(SUBSTR(dbfield,INSTR(dbfield,'.',2,4)+1,   INSTR(dbfield,'.',1,5) - INSTR(dbfield,'.',1,4)-1),'0'),27,'0'),

Note: This will take care of up to 5 levels (ie. 1.2.3.4.5.) (if you need more just duplicate the code). The zero padding is taken out to the full length of the field (27 in this example) to make sure things are properly ordered.

example:

1.2.3.     first ordering 000000000000000000000000001
2.3.4.     first ordering 000000000000000000000000002
11.2.3.    first ordering 000000000000000000000000011

Hope this helps,

Andy

-- 
 /---------------------------------------------------------------------------\
 | Andrew Dowswell     | "...They bring me numbness in their bright needles, |
 | Parke-Davis (CPO)   |  they bring me sleep. Now I have lost myself..."    |
 | dowswea_at_aa.wl.com   |                                                     |
 | (313) 996-1337      |             Sylvia Plath "Tulips"                   |
 \---------------------------------------------------------------------------/
 Disclaimer: The opinions of Parke-Davis are not necessarily my own, they
             should not be construed as mine and no flames should be directed 
             at me for something Parke-Davis has said or done.