请教大家一个难题!急!!!!!!!!!

Question

SELECT  TRIM(translate(A,'0,1,2,3,4,5,6,7,8,9,-','0,1,2,3,4,5,6,7,8,9,'))  AS  A  FROM  B  ORDER  BY  TRIM(translate(A,'0,1,2,3,4,5,6,7,8,9,-','0,1,2,3,4,5,6,7,8,9,'));

Answer 1

15:12:16 jlanzpa817>select * from test_number;A
----------
1
2
3
10
11
4
5
9-1
9-2
6
7A
----------
8
8-1
9
9-3
9-4
9-5
12
11-1已选择19行。已用时间:  00: 00: 00.50
15:12:38 jlanzpa817>select * from test_number order by 
15:12:44   2  to_number(decode(sign(instr(a,'-')),1,substr(a,1,instr(a,'-')-1),a));A
----------
1
2
3
4
5
6
7
8
8-1
9-1
9-2A
----------
9
9-4
9-5
9-3
10
11
11-1
12已选择19行。已用时间:  00: 00: 00.50

Answer 2

select * from 表名 order by to_number(replace(字段名,'-','.'))

Answer 3

select * from 表名 order by to_number(trim(replace(字段名,'-','.')))

Answer 4

ok:
15:17:16 jlanzpa817>select * from test_number order by 
15:17:16   2  to_number(decode(sign(instr(a,'-')),1,substr(a,1,instr(a,'-')-1),a)),
15:17:16   3  to_number(decode(sign(instr(a,'-')),1,substr(a,instr(a,'-')+1),0));A
----------
1
2
3
4
5
6
7
8
8-1
9
9-1A
----------
9-2
9-3
9-4
9-5
10
11
11-1
12

Answer 5

同意楼上，还是用replace简单。

Answer 6

bzszp的方法有一点问题:15:18:47 jlanzpa817>SELECT  *  FROM  test_number  ORDER  BY  TRIM(translate(A,'0,1,2,3,4,5,6,7,8,9,-
','0,1,2,3,4,5,6,7,8,9,'));A
----------
1
10
11
11-1
12
2
3
4
5
6
7A
----------
8
8-1
9
9-1
9-2
9-3
9-4
9-5已选择19行。已用时间:  00: 00: 00.60
15:20:14 jlanzpa817>

Answer 7

ATCG(ATCG) 的方法当有类似 '9-11'这样的数据就不行了.

Answer 8

这样不对
你需要转换为mm-dd格式
才能比较大小
下边的代码也只能比较到月
到天时也会出错
你可以模仿着在进行处理
select a from t_temp
order by
decode(substr
(
decode(substr( a  || 'x',2,1),
'0',a,'1',a,'2',a,
'-', '0' || a,
'x','0' || a || '-00')
|| 'x',3,1),'x', a || '-00','-', a
)