select * from table1 order by REPLACE(YourColumn,'津开发','1'),trim('号',trim('开发',trim('开发',TRIM ('津' FROM YourColumn))))
写成两条独立的查询,用 union all 合并不是更快吗, 还可以利用到index select * from tab where col like '津开发%' order by col union all select * from tab where col like '开发%' order by col
受不了。 select * from table order by substr(col,instr(col,']'))
“津开发[2003]2号”要保证数字后只有一个"号"的字符.用以下方法看是否可帮到你 create function getlength(a in varchar2) return number as num number; begin num:=lengthb(substr(a,instr(a,']')+1))-2; return num; end; /select ... from table_name group by substr(col_id,1,instr(col_id,'[')-1),substr(col_id,instr(col_id,']',1,2)+1,get(col_id));
效率肯定不好。
select * from tab where col like '津开发%' order by col
union all
select * from tab where col like '开发%' order by col
select * from table order by substr(col,instr(col,']'))
create function getlength(a in varchar2)
return number
as
num number;
begin
num:=lengthb(substr(a,instr(a,']')+1))-2;
return num;
end;
/select ... from table_name group by substr(col_id,1,instr(col_id,'[')-1),substr(col_id,instr(col_id,']',1,2)+1,get(col_id));