我的数据库B中有一个字段A类型为(VCHAR2)(某些值中有空格)值为
1, 2, 3, 10, 11, 4, 5, 9-1, 9-2, 6, 7, 8, 8-1, 9, 9-3, 9-4, 9-5, 12
请问如何SELECT出正确的顺序如:
1, 2, 3, 4, 5, 6, 7, 8, 8-1, 9, 9-1, 9-2, 9-3, 9-4, 9-5, 10, 11, 12
我这样写不对
SELECT TRIM(A) AS A FROM B ORDER BY TRIM(A)
---------------------------------------------------------------
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,'));
---------------------------------------------------------------
select * from 表名 order by to_number(trim(replace(字段名,'-','.')))
---------------------------------------------------------------
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-1
A
----------
9-2
9-3
9-4
9-5
10
11
11-1
12