Sorting numbers in a text field

From: <spudtheimpaler_at_gmail.com>
Date: 17 Jan 2007 06:42:32 -0800
Message-ID: <1169044951.942962.22440@q2g2000cwa.googlegroups.com>

Hi there...

Whilst I am sure this question has been asked in other forms before, I have searched the groups using google and not found an answer. This is my first question to this newsgroup, but not my first newgroup post to other groups.

I have a VARCHAR2 field which contains numbers. Order by sorts these lexigraphically (how /do/ you spell that?!). I have overcome this with the code below:

SELECT LPAD(

            text_col,
            (SELECT MAX( LENGTH(text_col) ) from table),
            ' ') AS Sorted

FROM table
ORDER BY Sorted;

This works great for only numbers, however some of my numbers contain suffixes, as can be seen in the example output below:

SORTED

   1
   3
   6
   7
  10
  34
  43

 3-A
 3-B
17-B

10 rows selected.

I am looking for a purely sql hint, or some direction, and am using oracle 9i.

Thank you in advance,

Mitch. Received on Wed Jan 17 2007 - 08:42:32 CST