SELECT id,Name FROM tab_name ORDER BY decode(Upper(substr(id,1,1)),'A',id,chr(id));
可以左填充0 SELECT id,Name FROM tab_name ORDER BY LPAD(id,'0',10)
create table tabname(a varchar2(10),b varchar2(10)); select * from tabname order by lpad(a,2)
SQL> select * from aa order by name;NAME ---------- 1 10 2 a a1 a26 rows selected解决方法: SQL> select * from aa order by decode(trim(translate(name,'0123456789',' ')),null,to_number(name));NAME ---------- 1 2 10 a a1 a26 rows selected
FROM tab_name
ORDER BY decode(Upper(substr(id,1,1)),'A',id,chr(id));
SELECT id,Name
FROM tab_name
ORDER BY LPAD(id,'0',10)
select * from tabname order by lpad(a,2)
----------
1
10
2
a
a1
a26 rows selected解决方法:
SQL> select * from aa order by decode(trim(translate(name,'0123456789',' ')),null,to_number(name));NAME
----------
1
2
10
a
a1
a26 rows selected