Re: Sorting numbers on a VARCHAR2 field

From: Andreas Necker <Andreas.Necker_at_isb-ka.de>
Date: Wed, 08 Nov 2000 12:54:37 +0100
Message-ID: <3A093EFD.94A208A@isb-ka.de>

hi,

Yong Huang wrote:
>
> Not if you have a number with more digits than some alphabetic words:
>
> SQL> select * from test;
>
> A
> ------------------------------
> test
> yong
> 1
> 132
> 15
> 10000
>
> 6 rows selected.
>
> SQL> select lpad(a,10,' ') new_a from test order by new_a;
>
> NEW_A
> ----------
> 1
> 15
> 132
> test
> yong
> 10000
>
> 6 rows selected.
>
> You want 10000 above "test", or see "test" and "yong" to be above 1, right?
> It's not.
>
> But I can't think of a way to solve the problem, at least not without
> creating another table for temporary storage.
>

this one brings the alphabetic entries to the top:

	select a, translate( lpad(a,10,' '),
	    '1234567890abcdefghijklmnopqrstuvwxyz',
	    '1234567890' ) new_a
	from test
	order by new_a;

A          NEW_A

---------- ----------
yong
test

1                   1
15                 15
132               132
10000           10000

and this one to the end:

	select a, nvl( rtrim( translate( lpad(a,10,' '),
	    '1234567890abcdefghijklmnopqrstuvwxyz',
	    '1234567890' )  ), '9999999999' ) new_a
	from test
	order by new_a;

A          NEW_A
---------- ----------
1                   1
15                 15
132               132
10000           10000
yong       9999999999
test       9999999999

-- 
Andreas Necker

ISB AG              Tel: +49 (0)721/82800-0
Karlstrasse 52-54   Fax: +49 (0)721/82800-82
76133 Karlsruhe     mailto:Andreas.Necker_at_isb-ka.de
Germany             http://www.isb-ka.de

Received on Wed Nov 08 2000 - 05:54:37 CST