一个关于oracle字段排序的问题

Question

order by myfield
应该就行了吧？

Answer 1

order by decode(myfield,'10','99')；
试试
我也在学习中

Answer 2

order by substr(fieldname,1,length(fieldname)-1)

Answer 3

order by to_number(decode(substr(fieldname,length(fieldname)),'M',substr(fieldname,1,length(fieldname)-1),fieldname))

Answer 4

--用最笨的办法：
--测试:
create table tb1(myField varchar2(10));
insert into tb1 values('5');
insert into tb1 values('10');
insert into tb1 values('2.3');
insert into tb1 values('2.3M');--写一个函数来处理字符串中的非数字
create  or  replace  function  f_test(str_source  in  varchar2)  
return  varchar2  
is 
  str_return  varchar2(10);  
  chr varchar2(1);
  i int := 0;
begin  
  Loop
    i :=i+1;
    chr := substr(str_source,i,1);
    if (chr > '0' and chr < '9') or chr = '.' then
      str_return := str_return||chr; 
    else
      str_return := str_return||'0';
      exit;
    end if;
    if i>=length(str_source) then
      exit;
    end if; 
  end  loop;  
  return  str_return;  
end;  
/  
select myfield from tb1 order by to_number(f_test(myfield));
/*结果如下
MYFIELD
----------
2.3
2.3M
5
10
*/drop table tb1;
drop function f_test;

Answer 5

SQL817>  select * from tb;A
----------
5
10
2.3
2.3M
2.3SGSQL817> select * from tb
  2  order by
  3  to_number
  4         (
  5           substr
  6           (
  7             a,1,length(a)-nvl
  8                           (
  9                             length
 10                             (
 11                               replace
 12                               (
 13                                 translate(a,'0123456789.','00000000000'),'0'
 14                               )
 15                             ),0
 16                           )
 17           )
 18         )
 19  /A
----------
2.3
2.3M
2.3SG
5
10

Answer 6

select to_number
       (
         substr
         (
           a,1,length(a)-nvl
                         (
                           length
                           (
                             replace
                             (
                               translate(a,'0123456789.','00000000000'),'0'
                             )
                           ),0
                         )
         )
       ) from tb如果字符相对固定如只有M,N,...,Z
可用：
order by to_number(replace(translate(myfield,'MN...Z','MM...M'),'M'));

Answer 7

上面一个贴，贴错内容了select * from tb
order by
to_number
       (
         substr
         (
           a,1,length(a)-nvl
                         (
                           length
                           (
                             replace
                             (
                               translate(a,'0123456789.','00000000000'),'0'
                             )
                           ),0
                         )
         )
       )如果字符相对固定如只有M,N,...,Z
可用：
order by to_number(replace(translate(myfield,'MN...Z','MM...M'),'M'));

Answer 8

K，搞这么复杂啊，好像ORACLE有个从字符到整型的转换函数吧