关于不规则列的排序问题

在日常的开发中会遇到这样的问题，某一列是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可同样适用。推荐使用第一种方法，因为第一种方法更方便。