Re: Sorting numbers in a text field

From: <spudtheimpaler_at_gmail.com>
Date: 17 Jan 2007 07:21:12 -0800
Message-ID: <1169047272.665494.157850@m58g2000cwm.googlegroups.com>

I have found my own answer, thank you.

Please see code below (note that the +2 on the default in the decode is because there is only ever a hyphen and letter, 2 characters. if you had a variable no of characters afterwards this will not work)

Thank you for any who looked over it

Regards,
Mitch.

COLUMN Sorted format a20

SELECT DECODE( INSTR(test,'-'), --test for hyphen

                             0, LPAD( test, --if no hyphen, add padding
to right align text/numbers
                                      (SELECT MAX( LENGTH(test) ) FROM
table),
                                      ' '
                                    ), --now use default (if hyphen
found ie INSTR test !=0
                            LPAD( test,
                                  ( SELECT MAX( LENGTH(test) ) FROM
table)+2,
                                  ' '
                                )
             )

AS Sorted
FROM table
ORDER BY Sorted; Received on Wed Jan 17 2007 - 09:21:12 CST