/*
sorting alphanumeric column.
say, column COL1 values with below:
C1
C2
C3
C10
after order by COL1,it turns out to be
C1
C10
C2
C3
but it should be
C1
C2
C3
C10
*/
/* create testing table */
CREATE TABLE TEST
(
COL0 CHAR(3)
);
/* insert testing data */
INSERT INTO TEST VALUES ('C1');
INSERT INTO TEST VALUES ('C2');
INSERT INTO TEST VALUES ('C3');
INSERT INTO TEST VALUES ('C4');
INSERT INTO TEST VALUES ('C5');
INSERT INTO TEST VALUES ('C6');
INSERT INTO TEST VALUES ('C7');
INSERT INTO TEST VALUES ('C8');
INSERT INTO TEST VALUES ('C9');
INSERT INTO TEST VALUES ('C10');
INSERT INTO TEST VALUES ('C11');
INSERT INTO TEST VALUES ('C12');
INSERT INTO TEST VALUES ('C13');
INSERT INTO TEST VALUES ('CE1');
INSERT INTO TEST VALUES ('CE2');
INSERT INTO TEST VALUES ('E1');
INSERT INTO TEST VALUES ('B8');
INSERT INTO TEST VALUES ('BE1');
INSERT INTO TEST VALUES ('BE2');
INSERT INTO TEST VALUES ('CE');
INSERT INTO TEST VALUES ('教');
INSERT INTO TEST VALUES ('教1');
INSERT INTO TEST VALUES ('教2');
INSERT INTO TEST VALUES ('教10');
/* using TRANSLATE Function to separate alphabets and numbers */
SELECT COL0 FROM
(
SELECT COL0,
TRIM(TRANSLATE(COL0,'1234567890',' ')) COL1,
TO_NUMBER(COALESCE(TRIM(TRANSLATE(COL0,
TRANSLATE(COL0,'1234567890',' '),
' ')
),NULL,'0')) COL2
FROM TEST
) T
ORDER BY COL1,COL2;
/* The Result */
COL0
--------
B8
BE1
BE2
C1
C2
C3
C4
C5
C6
C7
C8
C9
C10
C11
C12
C13
CE
CE1
CE2
E1
教
教1
教2
教10
