Re: SORTING PROBLEM WHEN NUMBERS ARE MIXED WITH LETTERS

What a daft way to want to sort data. Are you sure your application and/or database is designed right?
Anyway, you're not complete on what the data can contain and how you want it sorted. What your example shows is: - a bunch of digits, possibly followed by a bunch of letters - a bunch of letters possibly followed by a bunch of digits Are these the only possible combinations? If letters are followed by digits, how do you want the digits sorted?

Jaap.

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 - 04:42:21 CST