Re: Printing alpha fields in numeric order

A copy of this was sent to "Kevin Simon" <ksimon_at_fwi.com> (if that email address didn't require changing) On Wed, 26 Jan 2000 11:08:07 -0500, you 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
>
>

try something like this:

create table t ( cust_no varchar2(255) );

insert into t select user_id || username from all_users where rownum < 25;

select * from t
order by to_number( replace(translate( cust_no, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ$_', 'A' ), 'A', '') )
/

The translate turns A's into A's and every other character into nothing. The replace turns A's into nothing. Just add to the long string any character you want to get rid of and it'll get rid of them.

-- 
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation