/*
sorting alphanumeric column.
say, column COL0 values with below:
C1
C2
C3
C10
after order by COL0,it turns out to be
C1
C10
C2
C3
but it should be
C1
C2
C3
C10
1) sorting alphanumeric column support Multi-Byte character
2) sorting alphanumeric column support Single-Byte character
*/
/* Create testing table */
CREATE TABLE TEST
(
COL0 VARCHAR(6)
) IN USERSPACE1;
/* insert testing table */
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 ('教1');
INSERT INTO TEST VALUES ('教2');
INSERT INTO TEST VALUES ('教10');
/* 1) Multi-Byte character sorting */
WITH N(CID,RID,COL0,LENS,COL1,COL2) AS
(
SELECT 1 CID,RID,COL0,LENS,
CASE WHEN ASCII(SUBSTRING(COL0,1,1,CODEUNITS32)) NOT BETWEEN 48 AND 57 THEN
SUBSTRING(COL0,1,1,CODEUNITS32) ELSE '' END COL1,
CASE WHEN ASCII(SUBSTRING(COL0,1,1,CODEUNITS32)) BETWEEN 48 AND 57 THEN
SUBSTRING(COL0,1,1,CODEUNITS32) ELSE '' END COL2
FROM
(
SELECT ROW_NUMBER() OVER() RID,COL0,LENGTH(RTRIM(COL0)) LENS
FROM TEST
) T
UNION ALL
SELECT N.CID + 1,N.RID,N.COL0,N.LENS,
N.COL1||
CASE WHEN ASCII(SUBSTRING(COL0,N.CID + 1,1,CODEUNITS32))
NOT BETWEEN 48 AND 57 THEN
SUBSTRING(COL0,N.CID + 1,1,CODEUNITS32) ELSE '' END COL1,
N.COL2||
CASE WHEN ASCII(SUBSTRING(COL0,N.CID + 1,1,CODEUNITS32))
BETWEEN 48 AND 57 THEN
SUBSTRING(COL0,N.CID + 1,1,CODEUNITS32) ELSE '' END COL2
FROM N
WHERE N.CID + 1 <= N.LENS
)
SELECT COL0 FROM N
WHERE CID = LENS
ORDER BY COL1,INT(CASE WHEN COL2='' THEN '0' ELSE COL2 END);
/* And 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
/* Truncate testing table */
ALTER TABLE TEST ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE
/* 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');
/* 2) Single-Byte character sorting */
SELECT COL0
FROM
(
SELECT COL0,
LTRIM(RTRIM(TRANSLATE(COL0,'','0123456789'))) COL1,
LTRIM(RTRIM(TRANSLATE(UPPER(COL0),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'))) COL2
FROM TEST
) TMP
ORDER BY COL1,INT(CASE WHEN COL2='' THEN '0' ELSE COL2 END);
/* And the Result */
COL0
-----
B8
BE1
BE2
C1
C10
C11
C12
C13
C2
C3
C4
C5
C6
C7
C8
C9
CE
CE1
CE2
E1
