| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: SORTING PROBLEM WHEN NUMBERS ARE MIXED WITH LETTERS
Hi Rob,
IMHO it would be impossible to find simple way for sorting
varchars with letters and digits.
Just because there is one standart method (sorting by ASCII
or some national code) everywhere. Just look at sorted list of
your files (OS doesnt matter).
Having sayd that I just mean that if someone want to sort in other
way than standart one than he must describe the algorithm and ...
and implement it.
I'd like to offer some implementation with all its side-effects (as they
are),
and wonder if you will find it acceptable.
(And I would never ever would like to talk or (worst) meet with your DBA).
I also really wonder if there exists someone who knows better way out there?
Lets say that our values look like as StartingNumber, MiddleString and EndingNumber. And lets say the next: value has very big StartingNumber if it starts from letter, value has NULL MiddleString if it contains only digits, value has EndingNumber set to 0 if its last character is not digit.
Than if we will select your values ordered by StartingNumber, MiddleString and EndingNumber we will get 136b before 136, TESTRUN2 after TESTRUN but before TESTRUN11.
SELECT y, s_v_p1(y), s_v_p2(y), s_v_p3(y) FROM x
ORDER BY 2, 3, 4;
-
63 63 NULL 0
136b 136 b 0
137 137 NULL 0
ab 1000000000 ab 0
ab1 1000000000 ab 1
abc2 1000000000 abc 2
abc10 1000000000 abc 10
TESTRUN 1000000000 testrun 0
TestRun 1000000000 testrun 0
TESTRUN2 1000000000 testrun 2
testrun11 1000000000 testrun 11
NULL 1000000000 NULL 0
And here are String_Value_PartX functions:
CREATE OR REPLACE FUNCTION s_v_p1(value IN VARCHAR2)
RETURN NUMBER
AS
mid_chars VARCHAR2(255); mid_start NUMBER; MAX_VALUE NUMBER := 1000000000;
CREATE OR REPLACE FUNCTION s_v_p2(value IN VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
RETURN TRIM( LOWER( TRANSLATE( value,'0123456789',' ' ) ) );
END s_v_p2;
CREATE OR REPLACE FUNCTION s_v_p3(value IN VARCHAR2)
RETURN NUMBER
AS
mid_chars VARCHAR2(255);
mid_start NUMBER;
BEGIN
mid_chars := TRIM( TRANSLATE( value,'0123456789',' ' ) );
IF mid_chars IS NULL THEN
RETURN 0;
END IF;
mid_start := INSTR( value, mid_chars );
RETURN NVL( TO_NUMBER(
SUBSTR( value, mid_start + LENGTH( mid_chars ) ) ), 0);
EXCEPTION WHEN OTHERS THEN
RETURN 0;
END s_v_p3;
Regards
-- Rob Earl wrote:Received on Fri Dec 21 2001 - 01:08:25 CST
> 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
 |
 |