待排序数据:是一个名字为v的视图
按字母、数字分割查询:
SQL如下:
select data,
replace(data,replace(translate(data,'0123456789','##########'),'#',''),'') deptno,
replace(translate(data,'0123456789','##########'),'#','') ename
from vstranslate函数用法可参考
点击打开链接
不过我这样也是行的:
select data,
replace(data,replace(translate(data,'0123456789','#'),'#',''),'') deptno,
replace(translate(data,'0123456789','#'),'#','') ename
from v
既然能分割出来,那排序不就好做了吗。
按deptno排序:
select data from v order by replace(data,replace(translate(data,'0123456789','#'),'#',''),'')按ename排序:
select data from v order by replace(translate(data,'0123456789','#'),'#','')