我可以帮你解决掉带'_'的.select * from tb order by case when instr(col,'_') > 0 then substring(col,1,instr(col,'_')-1) else col end
不考虑中文,就是连_后面的通通都不要了这样 select EA_NAME from tb order by substring(EA_NAME,0,indexof(EA_NAME,'_'))
1 with t as ( 2 select '1A1' ea_name from dual 3 union all 4 select '1B3' from dual 5 union all 6 select '1B13' from dual 7 union all 8 select '1A2' from dual 9 union all 10 select '4B12' from dual 11 union all 12 select '2A3' from dual 13 union all 14 select '3A4' from dual 15 union all 16 select '3A5' from dual 17 union all 18 select '11A2' from dual 19 union all 20 select '11A12' from dual 21 union all 22 select '2A3_后门' from dual 23 ) 24 select * from t 25 order by 26 to_number(regexp_substr(ea_name,'^(\d{1,2})([a-z]{1})(\d{1,2})',1,1,'i',1)), 27 regexp_substr(ea_name,'^(\d{1,2})([a-z]{1})(\d{1,2})',1,1,'i',2), 28* to_number(regexp_substr(ea_name,'^(\d{1,2})([a-z]{1})(\d{1,2})',1,1,'i',3)) SQL> /EA_NAME ---------- 1A1 1A2 1B3 1B13 2A3 2A3_后门 3A4 3A5 4B12 11A2 11A12
ORA-00939 函数的参数过多
-- or select * from t order by regexp_replace(regexp_replace(ea_name,'(\d{1,2})([A-Z]{1})(\d{1,2})','0\1\20\3'),'0(\d{2})','\1')
还有一种情况就是EA_NAME为空的情况,这是空值的记录排在哪里?最前面还是最后面?
null 值在排序时被认作最大值。 可以使用 order by ... nulls first 或 order by ... nulls last 控制。
明白了,可是order by regexp_replace(regexp_replace(ea_name,'(\d{1,2})([A-Z]{1})(\d{1,2})','0\1\20\3'),'0(\d{2})','\1') 这句还没怎么看懂,能否给我解释一下,我想知道原理
不知道我这样理解的对不对,order by 后面就是利用正则将字母前后的数字全部变成两位,原来是两位的不变,不是两位,在前面补0? 呵呵,还没用过regexp_replace这个函数
select EA_NAME from tb order by substring(EA_NAME,0,indexof(EA_NAME,'_'))
1 with t as (
2 select '1A1' ea_name from dual
3 union all
4 select '1B3' from dual
5 union all
6 select '1B13' from dual
7 union all
8 select '1A2' from dual
9 union all
10 select '4B12' from dual
11 union all
12 select '2A3' from dual
13 union all
14 select '3A4' from dual
15 union all
16 select '3A5' from dual
17 union all
18 select '11A2' from dual
19 union all
20 select '11A12' from dual
21 union all
22 select '2A3_后门' from dual
23 )
24 select * from t
25 order by
26 to_number(regexp_substr(ea_name,'^(\d{1,2})([a-z]{1})(\d{1,2})',1,1,'i',1)),
27 regexp_substr(ea_name,'^(\d{1,2})([a-z]{1})(\d{1,2})',1,1,'i',2),
28* to_number(regexp_substr(ea_name,'^(\d{1,2})([a-z]{1})(\d{1,2})',1,1,'i',3))
SQL> /EA_NAME
----------
1A1
1A2
1B3
1B13
2A3
2A3_后门
3A4
3A5
4B12
11A2
11A12
ORA-00939 函数的参数过多
select * from t
order by regexp_replace(regexp_replace(ea_name,'(\d{1,2})([A-Z]{1})(\d{1,2})','0\1\20\3'),'0(\d{2})','\1')
null 值在排序时被认作最大值。
可以使用 order by ... nulls first 或 order by ... nulls last 控制。
明白了,可是order by regexp_replace(regexp_replace(ea_name,'(\d{1,2})([A-Z]{1})(\d{1,2})','0\1\20\3'),'0(\d{2})','\1')
这句还没怎么看懂,能否给我解释一下,我想知道原理
呵呵,还没用过regexp_replace这个函数