Re: Printing alpha fields in numeric order
Date: Thu, 27 Jan 2000 09:41:58 GMT
Message-ID: <86p3t6$u7q$1_at_nnrp1.deja.com>
Kevin,
Or, if the alpha character is always the last character and you can lock the table while you run the script, then the code below might provide the order you want.
DROP TABLE KEVIN;
CREATE TABLE KEVIN (KVAL VARCHAR2(8));
INSERT INTO KEVIN VALUES ('1');
INSERT INTO KEVIN VALUES ('2');
INSERT INTO KEVIN VALUES ('3');
INSERT INTO KEVIN VALUES ('4');
INSERT INTO KEVIN VALUES ('5');
INSERT INTO KEVIN VALUES ('6');
INSERT INTO KEVIN VALUES ('7');
INSERT INTO KEVIN VALUES ('8');
INSERT INTO KEVIN VALUES ('9');
INSERT INTO KEVIN VALUES ('10');
INSERT INTO KEVIN VALUES ('11');
INSERT INTO KEVIN VALUES ('12');
INSERT INTO KEVIN VALUES ('13');
INSERT INTO KEVIN VALUES ('14');
INSERT INTO KEVIN VALUES ('15');
INSERT INTO KEVIN VALUES ('16');
INSERT INTO KEVIN VALUES ('17');
INSERT INTO KEVIN VALUES ('18');
INSERT INTO KEVIN VALUES ('19');
INSERT INTO KEVIN VALUES ('20');
INSERT INTO KEVIN VALUES ('101A');
INSERT INTO KEVIN VALUES ('110B');
INSERT INTO KEVIN VALUES ('112A');
INSERT INTO KEVIN VALUES ('123A');
INSERT INTO KEVIN VALUES ('141A');
INSERT INTO KEVIN VALUES ('145B');
INSERT INTO KEVIN VALUES ('163B');
INSERT INTO KEVIN VALUES ('167C');
INSERT INTO KEVIN VALUES ('186A');
INSERT INTO KEVIN VALUES ('189A');
INSERT INTO KEVIN VALUES ('204B');
INSERT INTO KEVIN VALUES ('101');
INSERT INTO KEVIN VALUES ('110');
INSERT INTO KEVIN VALUES ('112');
INSERT INTO KEVIN VALUES ('123');
INSERT INTO KEVIN VALUES ('141');
INSERT INTO KEVIN VALUES ('145');
INSERT INTO KEVIN VALUES ('163');
INSERT INTO KEVIN VALUES ('167');
INSERT INTO KEVIN VALUES ('186');
INSERT INTO KEVIN VALUES ('189');
INSERT INTO KEVIN VALUES ('204');
INSERT INTO KEVIN VALUES ('11C');
UPDATE KEVIN SET KVAL = LPAD(KVAL,7,' ');
UPDATE KEVIN SET KVAL = LPAD(KVAL,8,' ')
WHERE SUBSTR(KVAL,7,1) IN ('A', 'B', 'C', 'D');
UPDATE KEVIN SET KVAL = KVAL || ' '
WHERE LENGTH(KVAL) = 7;
SELECT LTRIM(KVAL) FROM KEVIN ORDER BY KVAL ASC; UPDATE KEVIN SET KVAL = RTRIM(LTRIM(KVAL)); Output looks like this:
SQL> SELECT LTRIM(KVAL) FROM KEVIN ORDER BY KVAL ASC; LTRIM(KV
1
2
3
4
5
6
7
8
9
10
11
11C
12
13
14
15
16
17
18
19
20
101
101A
110
110B
112
112A
123
123A
141
141A
145
145B
163
163B
167
167C
186
186A
189
189A
204
204B
43 rows selected.
Regards,
Paul
In article <s8u71qro7q593_at_corp.supernews.com>,
"Kevin Simon" <ksimon_at_fwi.com> 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
>
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Thu Jan 27 2000 - 10:41:58 CET