Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> SORTING PROBLEM WHEN NUMBERS ARE MIXED WITH LETTERS
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.
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 Thu Dec 20 2001 - 10:38:23 CST