有个字段 内容如下:自动酸值
自动酸值
闪点(开口)
初溜点
%5回收温度
%15回收温度
%20回收温度
%25回收温度
%30回收温度
%35回收温度我要按以百分号开头的数字大小进行排序,sql语句怎么写,
注意:1、字段值中存在不以百分号数字开头的字符。2、截取数字后如果按首字母排序的话5%不能排到第一个,只能排到以4开头的数字以后;
自动酸值
闪点(开口)
初溜点
%5回收温度
%15回收温度
%20回收温度
%25回收温度
%30回收温度
%35回收温度我要按以百分号开头的数字大小进行排序,sql语句怎么写,
注意:1、字段值中存在不以百分号数字开头的字符。2、截取数字后如果按首字母排序的话5%不能排到第一个,只能排到以4开头的数字以后;
select *
from emp
order by decode(substr(name, 1, 1),
'%',
to_number(regexp_replace(name, '[^0-9]')),
null) nulls first
SELECT '自动酸值' NAME FROM dual UNION ALL
SELECT '自动酸值' FROM dual UNION ALL
SELECT '闪点(开口)' FROM dual UNION ALL
SELECT '初溜点' FROM dual UNION ALL
SELECT '%5回收温度' FROM dual UNION ALL
SELECT '%15回收温度' FROM dual UNION ALL
SELECT '%20回收温度' FROM dual UNION ALL
SELECT '%25回收温度' FROM dual UNION ALL
SELECT '%30回收温度' FROM dual UNION ALL
SELECT '%35回收温度' FROM dual
)
select name from tab
order by replace(name,'%','')
NAME
---------------------------
%15回收温度
%20回收温度
%25回收温度
%30回收温度
%35回收温度
%5回收温度
初溜点
自动酸值
自动酸值
闪点(开口)
SELECT '自动酸值' NAME FROM dual UNION ALL
SELECT '自动酸值' FROM dual UNION ALL
SELECT '闪点(开口)' FROM dual UNION ALL
SELECT '初溜点' FROM dual UNION ALL
SELECT '%05回收温度' FROM dual UNION ALL
SELECT '%15回收温度' FROM dual UNION ALL
SELECT '%20回收温度' FROM dual UNION ALL
SELECT '%25回收温度' FROM dual UNION ALL
SELECT '%30回收温度' FROM dual UNION ALL
SELECT '%35回收温度' FROM dual
)select *
from tab
order by decode(substr(name, 1, 1),
'%',
to_number(substr(name, 2, 2)),
null) nulls first;
这样的话 太呆板了 你的百分比必须是2位 如5% 得写成 05%
SELECT '自动酸值' NAME FROM dual UNION ALL
SELECT '自动酸值' FROM dual UNION ALL
SELECT '闪点(开口)' FROM dual UNION ALL
SELECT '初溜点' FROM dual UNION ALL
SELECT '%5回收温度' FROM dual UNION ALL
SELECT '%15回收温度' FROM dual UNION ALL
SELECT '%20回收温度' FROM dual UNION ALL
SELECT '%25回收温度' FROM dual UNION ALL
SELECT '%30回收温度' FROM dual UNION ALL
SELECT '%35回收温度' FROM dual
)select *
from tab
order by decode(substr(name, 1, 1),
'%',
to_number(substr(name,
instr(name, '%')+1 ,
instr(name, '回',1) - instr(name, '%')-1)),
null) nulls first;
改为 nulls last
2 SELECT '自动酸值' NAME FROM dual UNION ALL
3 SELECT '自动酸值' FROM dual UNION ALL
4 SELECT '闪点(开口)' FROM dual UNION ALL
5 SELECT '初溜点' FROM dual UNION ALL
6 SELECT '%5回收温度' FROM dual UNION ALL
7 SELECT '%15回收温度' FROM dual UNION ALL
8 SELECT '%20回收温度' FROM dual UNION ALL
9 SELECT '%25回收温度' FROM dual UNION ALL
10 SELECT '%30回收温度' FROM dual UNION ALL
11 SELECT '%35回收温度' FROM dual
12 )
13 select name from tab
14 order by instr('%',1,1), lpad(substr(name,instr(name,'%',1)+1,2),10,'0');NAME
------------------------------------
%15回收温度
%20回收温度
%25回收温度
%30回收温度
%35回收温度
%5回收温度
初溜点
自动酸值
自动酸值
闪点(开口)已选择10行。