怎么取varchar2类型数据的最大值？

Question

我有个table，有一列是varchar2类型的，要存'1.1.2'这样的数据，比如，这列原本有1.1~1.10的数据，我用
select max(wbs) from table查出来的最大值却是1.9，实际上应该是1.10，请问有什么方法可以拿到这类数据的最大值呢？

Answer 1

大哥，我认为这是版本控制工具数据表中的一个列，
1.9应该比1.10小按长度取也不合理，
因为1.1.2应该比1.9小整个存储过程处理可能要方便，写sql也行，但是费事

Answer 2

呵呵，是的，数据库的逻辑也是这么认为。
LZ可以通过自定义函数来实现这个功能~
就是自己写个类似于MAX的函数

Answer 3

直接写SQL是有点麻烦, 建议加个字段, 表示目录'1.1.2'是第几级(比如'1.1.2'是第三级), 这样处理起来会方便.

Answer 4

select max(to_number(wbs)) from table

Answer 5

不知道函数 to_number 对于 1.1.2 这样的VARCHAR2类型转换的结果是多少？这样转换会出错吧？

Answer 6

SQL> select to_number('1.12') from dual;TO_NUMBER('1.12')
-----------------
             1.12SQL>

Answer 7

select to_number('1.1.2') from dual;如果是1.1.2 两个小数点呢？

Answer 8

如果是1.1.2 两个小数点呢？去除一个小数点吧,取最大值还是要转成数字再取max

Answer 9

是的，table中的wbs列值会有多个小数点，例如1.1.1.1.1.1这样的，当然，还会有更长的

Answer 10

路过。没有想到解决办法。好久不写繁杂的SQL语句了。有点怀念过去的日子。

Answer 11

简单的方法没有，倒是可以通过自定义聚合函数来实现，随便写了一个max_version的聚合函数，凑合看吧 SQL> select * from test;ID
----------
1.2.1
1.1
1.2
1.10
1.12
1.1.2
1.1.1
1.1.128 rows selected.SQL> select max_version(id) from test;MAX_VERSION(ID)
--------------------------------------------------------------------------------
1.12SQL>
create or replace function get_max_version
        ( value1 in varchar2,
          value2 in varchar2 
        ) return varchar2 is 
          v_num1 int;        
          v_num2 int;        
          v_pos int;         
          v_value1 varchar2(2000) ;
          v_value2 varchar2(2000) ;
       begin                       
         v_value1 := value1;       
         v_value2 := value2;                                                                   
         if value1 is null or value1 = '' then
            return value2;                    
         elsif value2 is null or value2 = '' then
            return value1;                       
         else                                    
            while (length(v_value1) > 0 and length(v_value2) > 0) loop
              v_pos := instr(v_value1, '.', 1, 1);
              if v_pos > 0 then                   
                 v_num1 := to_number(substr(v_value1, 0, v_pos-1)) ;
                 v_value1 := substr(v_value1, v_pos+1);             
              else                                                  
                 v_num1 := to_number(v_value1);                     
                 v_value1 := '';                                    
              end if;                                               
              v_pos := instr(v_value2, '.', 1, 1);                  
              if v_pos > 0 then                                     
                 v_num2 := to_number(substr(v_value2, 0, v_pos-1)) ;
                 v_value2 := substr(v_value2, v_pos+1);             
              else                                                  
                 v_num2 := to_number(v_value2);                     
                 v_value2 := '';                                    
              end if;                                               
              if v_num1 > v_num2 then                               
                 return value1;                                     
              elsif v_num1 < v_num2 then
                 return value2;
              end if;
           end loop;
           if length(v_value1) = 0 then
              return value2;
           else
              return value1;
           end if;
        end if;
    end;
/create or replace type toversion as object                                   
(                                                          
  max_version varchar2(200),                               
  static function ODCIAggregateInitialize                  
  ( actx in out toversion                                  
  ) return number,                                         
  member function ODCIAggregateIterate                     
  ( self in out toversion,                                 
    value in varchar2                                      
  ) return number,                                         
  member function ODCIAggregateTerminate                   
 ( self in out toversion,                                  
    returnValue out varchar2,                              
    flags in varchar2                                      
  ) return number,                                         
  member function ODCIAggregateMerge                       
  ( self in out toversion,                                 
    ctx2 in toversion                                      
  ) return number                                          
);                                                      
/                      create or replace type body toversion as
         static function ODCIAggregateInitialize
             ( actx in out toversion              
             ) return number is                   
             begin                                
               actx := toversion('');             
                return ODCIConst.Success;          
              end;                                 
          member function ODCIAggregateTerminate 
            ( self in out toversion,               
              returnValue out varchar2,            
              flags in varchar2                    
            ) return number is                     
            begin                                  
              returnValue := self.max_version;     
              return ODCIConst.Success;            
            end;                                   
          member function ODCIAggregateIterate     
            ( self in out toversion,               
              value in varchar2                    
            ) return number is                     
            begin                                  
              if self.max_version is null then     
                 self.max_version := value;        
              else                                 
                 self.max_version := get_max_version(self.max_version, value);
              end if;
              return ODCIConst.Success;
            end;
          member function ODCIAggregateMerge
            ( self in out toversion,
              ctx2 in toversion
            ) return number is
            begin
              self.max_version := get_max_version(self.max_version, ctx2.max_version);
              return ODCIConst.Success;
            end;
    end;
/create or replace function max_version
( x varchar2) return varchar2
parallel_enable
aggregate using toversion;
/执行完成就好了，挺烦的，没想到啥好方法能支持较多层次的比较
自定义的好处就是逻辑定义语句好写好理解

Answer 12

10g可以用正则匹配替换，有点像perl或者ruby的那种
不是的话照这个思路写函数，应该不过10行