100分求一个字符数字混合时排序问题～～～～

Question

2006.5.林铬移交
GC12133
GPRS01002
GPRS010021
HNUN(2006)移094号
HNUN(2006)移095号
HNUN(2006)移1024号
HNUN(2006)移1026号
HNUN(2006)移1032号
HNUN(2006)移1033号
HNUN(2006)移115号
HNUN(2006)移116号
HNUN(2007)网建099号
HNUN(2007)网建101号
HNUN(2007)网建102号
HNUN(2007)网建1033
HNUN(2007)网建103号
HNUN(2007)网建1049号
HNUN(2007)网建104号
HNUN(2007)网建1050号
HNUN(2007)网建1051号
HNUN(2007)网建1052号
HNUN(2007)网建105号
数据库中一字段有这些类型的数据，
有“2006.5.林铬移交”，“GC12133”，这种不规则编号
也有如HNUN(2007)网建102号这样有一定规则的编号，
oracle的默认排序就是上面这种，怎么样才能使HNUN(2007)网建1049号这种排到1000多号去而不是插在100多号的中间？
即
HNUN(2007)网建099号
HNUN(2007)网建101号
HNUN(2007)网建102号
HNUN(2007)网建103号
HNUN(2007)网建104号
HNUN(2007)网建105号
......
......
HNUN(2007)网建1033
HNUN(2007)网建1049号
HNUN(2007)网建1050号
HNUN(2007)网建1051号
HNUN(2007)网建1052号

Answer 1

给你个方法参考：
DROP TABLE tt;
CREATE TABLE tt (n VARCHAR2(50));
insert into tt values('2006.5.林铬移交 ');
insert into tt values('GC12133 ');
insert into tt values('GPRS01002 ');
insert into tt values('GPRS010021 ');
insert into tt values('HNUN(2006)移094号 ');
insert into tt values('HNUN(2006)移095号 ');
insert into tt values('HNUN(2006)移1024号 ');
insert into tt values('HNUN(2006)移1026号 ');
insert into tt values('HNUN(2006)移1032号 ');
insert into tt values('HNUN(2006)移1033号 ');
insert into tt values('HNUN(2006)移115号 ');
insert into tt values('HNUN(2006)移116号 ');
insert into tt values('HNUN(2007)网建099号 ');
insert into tt values('HNUN(2007)网建101号 ');
insert into tt values('HNUN(2007)网建102号 ');
insert into tt values('HNUN(2007)网建1033号 ');
insert into tt values('HNUN(2007)网建103号 ');
insert into tt values('HNUN(2007)网建1049号 ');
insert into tt values('HNUN(2007)网建104号 ');
insert into tt values('HNUN(2007)网建1050号 ');
insert into tt values('HNUN(2007)网建1051号 ');
insert into tt values('HNUN(2007)网建1052号 ');
insert into tt values('HNUN(2007)网建105号 ');
COMMIT;
 
SELECT n 
  FROM TT
 ORDER BY REGEXP_REPLACE(n,'[0-9]+号',''),CASE
            WHEN N LIKE 'HNUN(2006)移%' THEN
             LPAD(LTRIM(N, 'HNUN(2006)移'), 10, '0')
            WHEN n LIKE 'HNUN(2007)网建%' THEN
             LPAD(LTRIM(N, 'HNUN(2007)网建'), 10, '0')
          END;
输出：
2006.5.林铬移交 
GC12133 
GPRS01002 
GPRS010021 
HNUN(2006)移094号 
HNUN(2006)移095号 
HNUN(2006)移115号 
HNUN(2006)移116号 
HNUN(2006)移1024号 
HNUN(2006)移1026号 
HNUN(2006)移1032号 
HNUN(2006)移1033号 
HNUN(2007)网建099号 
HNUN(2007)网建101号 
HNUN(2007)网建102号 
HNUN(2007)网建103号 
HNUN(2007)网建104号 
HNUN(2007)网建105号 
HNUN(2007)网建1033号 
HNUN(2007)网建1049号 
HNUN(2007)网建1050号 
HNUN(2007)网建1051号 
HNUN(2007)网建1052号 

Answer 2

SQL> SELECT * FROM T;CNAME
--------------------------------------------------------------------------------
HNUN(2006)094
HNUN(2006)095
HNUN(2006)1024
HNUN(2006)1026
HNUN(2006)1032
HNUN(2006)1033
HNUN(2006)115
HNUN(2006)1168 rows selectedSQL> SELECT CNAME FROM T ORDER BY TO_NUMBER(TRANSLATE(CNAME,'0123456789'||CNAME,'0123456789'));CNAME
--------------------------------------------------------------------------------
HNUN(2006)094
HNUN(2006)095
HNUN(2006)115
HNUN(2006)116
HNUN(2006)1024
HNUN(2006)1026
HNUN(2006)1032
HNUN(2006)10338 rows selected

Answer 3

那个，REGEXP_REPLACE这个函数9i里貌似没有，
上网查了一下，好像是10g才加进来的，
9i里有类似函数么？

Answer 4

SELECT n ,rtrim(n,'0123456789号')
  FROM TT
 ORDER BY rtrim(n,'0123456789号'),CASE
            WHEN N LIKE 'HNUN(2006)移%' THEN
             LPAD(LTRIM(N, 'HNUN(2006)移'), 10, '0')
            WHEN n LIKE 'HNUN(2007)网建%' THEN
             LPAD(LTRIM(N, 'HNUN(2007)网建'), 10, '0')
          END;