关于不规则列的排序问题

时间:2022-10-01 00:51:15

在日常的开发中会遇到这样的问题,某一列是varchar2类型不是NUMBER类型的,应客户需求要对这样的列进行排序,如果是纯数字或者是纯字符这都不是个难题,问题在于什么都有,如果简单的用to_number(),或者是to_char()来处理的话,不能达到预期的效果。

常见的问题有两种: 

  1.统一用to_number()来处理,当遇到oracle不能转化为数字的时候会报错。例如:

 关于不规则列的排序问题

2. 统一用to_char()来处理的话,不会报错,但是效果不尽如人意,例如下面所示: 

关于不规则列的排序问题

 

 

为了解决上述两种问题,需要增加一个虚拟列,这列可以用to_number()来排序,达到按数字由大到小的顺序来排列的目的。

首先我们需要明确,如果某一行的该列的值不能转化为数字,应该怎么处理,排在队伍的最前边还是在最后边,我们假设能够转化为数字的居多,这样把不能转化为数字的排在队伍的后边。

为了说得明白一点我们对上述数据做些调整,通过查询语句:

SELECT *FROM t_sort ORDER BY ID ;

得出下面所示的结果:

 

关于不规则列的排序问题

 

 

 现在我们要对上述结果进行排序,首先增加一个伪劣,对不是数字的项进行转化,假如将非数字的列转化为‘9999’则可以这样来进行:关于不规则列的排序问题

 

 这样的话我们就可以按照col_order这个伪劣来排序,像下面这样: 

 

关于不规则列的排序问题

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

到此问题基本解决了,但是在转化的过程中,对于检测是否是纯数字的问题,还可以可以用下面的函数来实现:DECODE+TRANSLATE+TRIM 来实现:

关于不规则列的排序问题

 

 

 该函数对于较低版本的oracle可同样适用。推荐使用第一种方法,因为第一种方法更方便。