Re: Printing alpha fields in numeric order

Kevin Simon wrote:
>
> I have a table which has a field which is typically numeric (customer
> number), but is defined as varchar(8). Before you say anything - I didn't
> design this.
>
> Anyway, I've got some people who want to print a report in numeric order,
> but it is coming out in alpha order (1, 10, 100, 101, ..., 109, 11, 110,
> 111, .... 199, 2, 20, 200, ....).
>
> My problem is this - there are a couple of "strays" out there which are not
> numeric. If I use the to_number function, it bombs on me because it cant
> convert "110A" to a numeric field.
>
> Is there a way that I can get around this? The data for these non-numeric
> customer numbers is throughout our system, so deleting or modifying that one
> to remove the offending parties is not a preferred option.
>
> My preferred option would be to come up with a function that returns the
> numeric portion of the field, then have that in the ORDER BY clause of my
> query, such as:
> select CUST_NO, NAME, ... FROM CUST ORDER BY function(CUST_NO)
>
> Any ideas?
>
> TIA,
> Kevin

Here's a SQL Plus script to try:

Drop table A;
Create table A(C1 Varchar2(8));

insert into A values ('1');
insert into A values ('10');
insert into A values ('100');
insert into A values ('110');
insert into A values ('05');
insert into A values ('001');
insert into A values ('9');
insert into A values ('09');
insert into A values ('90');
insert into A values ('1A');
insert into A values ('A1');
insert into A values ('01');
insert into A values ('010');

SELECT C1, LPAD(LTRIM(C1,'0'),8) "Order by" FROM A   ORDER BY LPAD(LTRIM(C1,'0'),8); This is what you get back:

C1 Order by
-------- --------

1               1
001             1
01              1
05              5
9               9
09              9
10             10
010            10
1A             1A
90             90
A1             A1
100           100
110           110

Steve Cosner
http://members.aol.com/stevec5088 Received on Wed Jan 26 2000 - 17:52:05 CET