Printing alpha fields in numeric order

From: Kevin Simon <ksimon_at_fwi.com>
Date: Wed, 26 Jan 2000 11:08:07 -0500
Message-ID: <s8u71qro7q593_at_corp.supernews.com>

[Quoted] [Quoted] I have a table which has a field which is typically numeric (customer [Quoted] number), but is defined as varchar(8). Before you say anything - I didn't [Quoted] design this.

Anyway, I've got some people who want to print a report in numeric order, [Quoted] but it is coming out in alpha order (1, 10, 100, 101, ..., 109, 11, 110, [Quoted] 111, .... 199, 2, 20, 200, ....).

My problem is this - there are a couple of "strays" out there which are not [Quoted] numeric. If I use the to_number function, it bombs on me because it cant [Quoted] convert "110A" to a numeric field.

Is there a way that I can get around this? The data for these non-numeric [Quoted] customer numbers is throughout our system, so deleting or modifying that one [Quoted] to remove the offending parties is not a preferred option.

My preferred option would be to come up with a function that returns the [Quoted] numeric portion of the field, then have that in the ORDER BY clause of my [Quoted] query, such as:

    select CUST_NO, NAME, ... FROM CUST ORDER BY function(CUST_NO)

Any ideas?

TIA,
Kevin Received on Wed Jan 26 2000 - 17:08:07 CET