Re: SORTING PROBLEM WHEN NUMBERS ARE MIXED WITH LETTERS

Hi

   try puting a
   select ....
   from..... where .....
   order by lower(column_name) asc;

 -Regards,
  Prakash C N

John Russell <netnews_at_johnrussell.mailshell.com> wrote in message news:<alc42u0fm1mhbvl7c6g62ue64llm786vvg_at_4ax.com>...
> I guess you could treat everything as a string, find the length of the
> longest value, then go back and pad all the true numbers with leading
> zeros. Then use the results of that in the ORDER BY, e.g. ORDER BY
> my_mangling_function(colname, maxlength).
>
> John
>
> On 20 Dec 2001 08:38:23 -0800, earlrc_at_juno.com (Rob Earl) wrote:
>
> >Since I didn't get any responses on this before, I thought I would
> >repost it so that it was higher up in the list. Can anyone out there
> >help me with this one??? Thanks much.
> >
> >=====================
> >Recently I have been presented with a problem of figuring out how to
> >display information from a table which has a VARCHAR2 field that can
> >contain numbers as well as alphanumeric characters. When just doing a
> >simply ORDER BY the fields is sorted in an ASCII way in which numbers
> >are out of numeric order and are in order of there ASCII
> >representation. I understand why the problem is occurring, but I'm
> >stuck trying to find an easy way in SQL to sort these values properly.
> > To make things a little tricker, there can be a mixture of
> >alphanumeric characters as well as numbers. Here is some example data
> >as seen with just a plain ORDER BY sort:
> >
> >136b
> >137
> >138
> >220
> >238
> >239
> >60
> >61
> >62
> >63
> >TESTRUN
> >TESTRUN2
> >TestRun
> >ab
> >abc10
> >abc2
> >
> >Through searching the USENET I did manage to come up with an SQL
> >statement that comes really really close to solving the problem:
> >
> >select NAME, nvl( trim (trailing from translate( lpad(NAME,40,' '),
> > '1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
> > '1234567890' ) ), NAME) new_a
> > from table1
> > order by new_a;
> >
> >That statement returns the following:
> >
> >60
> >61
> >62
> >63
> >137
> >138
> >220
> >238
> >239
> >136b
> >abc2
> >abc10
> >TESTRUN2
> >TESTRUN
> >TestRun
> >ab
> >
> >This was really close, but what the statement works best with is if
> >the field only contains JUST numbers or JUST letters. If there is a
> >mixture of both it kind of gets screwed up. The line out of place is
> >the 136b. It really should be right before the 137 line.
> >
> >This seems like it should be a real common problem, considering this
> >type of data must exist in many systems. Does anyone out there have
> >any idea how I could possibly get my data to sort properly? When I
> >say properly, I mean that it would be sorted like the following:
> >
> >60
> >61
> >62
> >63
> >136b
> >137
> >138
> >220
> >238
> >239
> >abc2
> >abc10
> >TESTRUN2
> >TESTRUN
> >TestRun
> >ab
> >
> >In a nutshell, this means that to be sorted properly all lines
> >starting with a number would come first, followed by lines starting
> >with letters. If the letter lines have numbers, they would proceed
> >any lines that have ONLY letters. This seems like a fairly normal
> >sort order to me.
> >
> >Thanks in advance for any help. I appreciate it greatly.
> >
> >Rob
Received on Sat Dec 22 2001 - 00:23:16 CST